topic Re: RANKX for the last 12 months, but single month selected in slicer in DAX Commands and Tips

RANKX for the last 12 months, but single month selected in slicer

N_R_000 — Sat, 30 Jan 2021 20:36:37 GMT

I'd like to show the rank of a months sales based on only the previous 12 months.

The kicker is that there's a single selection 'Month Year' slicer in use here.

So, I just need a way to expand the RANKX dates to the previous 12 months (previous based from today, not the selected month).

I can't use ALL('Calendar'[dates]) because there is 3 years worth sales of data in total and I'm only interested in the last 1 year.

Would something like this do the trick? Worth mentioning that I need this value to go in a standalone Card, not a monthly grouped table.

Monthly Sales Rank = RANKX(FILTER('Calendar', <PREVIOUS 12 MONTHS?> ), CALCULATE([Sum Of Sales]),,DESC, DENSE)

Re: RANKX for the last 12 months, but single month selected in slicer

OwenAuger — Sun, 31 Jan 2021 02:06:28 GMT

N_R_000

You're on the right track. I would write the measure like this:

Monthly Sales Rank = VAR SumOfSales = [Sum of Sales] VAR Result = CALCULATE ( RANKX ( VALUES ( 'Calendar'[Month Year] ), [Sum of Sales], // Evaluated for each Month Year SumOfSales, // Fixed value for month filtered on slicer DESC, DENSE ), // 12 months ending today. //You may want to tweak to ensure complete calendar months etc DATESINPERIOD ( 'Calendar'[Date], TODAY (), -12, MONTH ) ) RETURN Result

The SumOfSales variable stores the sales value for the month selected on the slicer.

Then the RANKX calculation is wrapped in a CALCULATE which changes the date filter to 12 months ending today.

Does this give you the expected result?

Re: RANKX for the last 12 months, but single month selected in slicer

N_R_000 — Sun, 31 Jan 2021 21:17:54 GMT

Hi Owen,

Thanks for the detailed explaination.

I just made a small tweak to DATESINPERIOD to swap TODAY() for a 'EndOfDateRange' measure and that give me the full months I need.

Thank you so much for your help!