Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
N_R_000
Helper I
Helper I

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

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.

N_R_000_0-1612038823159.png

 

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)

 

N_R_000_1-1612038845281.png

 

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors