topic Re: Month on Month Rank Comparison in DAX Commands and Tips

Month on Month Rank Comparison

Anonymous — Tue, 30 Jun 2020 11:27:57 GMT

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

Re: Month on Month Rank Comparison

Anonymous — Mon, 06 Jul 2020 02:22:42 GMT

Hi Anonymous , Greg_Deckler , Pragati11 , Mariusz , amitchandak

Could you please look into the threat. Need your help guys.

Regards

Uphar

Re: Month on Month Rank Comparison

amitchandak — Mon, 06 Jul 2020 02:53:49 GMT

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.