Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |