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

[N_R_000]

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)

[OwenAuger · Accepted solution]

@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?

[N_R_000]

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!