Forum Discussion
Month on Month Rank Comparison
Hi,
If you could also help me with Month on Month Rank change DAx.. My rank is changing month on month basis for same group.
I want to create a measure for Trend and check the Rank difference of this month to last month (Trend)
Last Month Ranking
| Month | Unit | Divison Ranking check | Average of Star Rating |
| Feb | Group12 | 1 | 3 |
| Feb | Group11 | 2 | 2.76 |
| Feb | Group10 | 3 | 2.62 |
| Feb | Group9 | 4 | 2.57 |
| Feb | Group8 | 5 | 2.36 |
| Feb | Group7 | 6 | 2.33 |
| Feb | 7 | 2.29 | |
| Feb | Group5 | 8 | 2.22 |
| Feb | Group4 | 9 | 2.11 |
| Feb | 10 | 2.06 | |
| Feb | Group1 | 11 | 2 |
| Feb | Group2 | 11 | 1.95 |
| Feb | Group3 | 12 | 1.8 |
| Feb | TBA | 13 |
This month ranking
| Month | Unit | Divison Ranking check | Trend |
| Mar | Group12 | 13 | 12 |
| Mar | Group11 | 7 | 5 |
| Mar | Group10 | 4 | 1 |
| Mar | Group9 | 3 | -1 |
| Mar | Group8 | 3 | -2 |
| Mar | Group7 | 3 | -3 |
| Mar | 4 | -3 | |
| Mar | Group5 | 10 | 2 |
| Mar | Group4 | 2 | -7 |
| Mar | 9 | -1 | |
| Mar | Group1 | 9 | -2 |
| Mar | Group2 | 8 | -3 |
| Mar | Group3 | 9 | -3 |
| Mar | TBA | 4 | -9 |
Tend is representing this month to last month rank difference.. I am new in powerbi so not able to apply the Time intellegence function..
Anonymous Pragati11
Please help.
Regards
Uphar
2 Replies
Hi Anonymous , Greg_Deckler , Pragati11 , Mariusz , amitchandak
Could you please look into the threat. Need your help guys.
Regards
Uphar
- amitchandak
Super User
Anonymous , please try like this example. I have not tested this
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) MTD RANK = Rankx(all(Table[Unit],Table[Divison]),[MTD Sales],,desc,dense) LMTD RANK = Rankx(all(Table[Unit],Table[Divison]),[last MTD Sales],,desc,dense) Diff =[MTD RANK] -[LMTD RANK]To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
