I have a question for dax in excel PowerPivot. As it is not Power BI, if you have a solution, please avoid using variables.
I am still pretty new to this but I am working with parent child structures for date grouping. I want to rank the date weekly with best sales based on three scenarios:
- With public holidays
- Without public Holidays
- Only weekdays
My structure for dates are as following
Year, -> Months, -> Weeks, -> weekday types (weekday, weekend, public holiday) -> Dates, Weekdays
I have a measure for the case with public holidays that looks like this
=rankx( (filter (all ('Dates'[Week.NO],'Dates'[Week.NO]=max('Dates'[Week.NO])), ([Sales])
Where [sales] is a calculated measure that looks like this: =sum('Sales'[Sales])
I tried to make filtered calculated measures and substitute them with [Sales] for each scenario but it did not work. How can I go about this?