Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Solved! Go to Solution.
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?
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?
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!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |