cancel
Showing results for
Did you mean:
Highlighted
Helper II

## 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..

Regards

Uphar

2 REPLIES 2
Highlighted
Helper II

Regards

Uphar

Highlighted
Super User IV

## Re: Month on Month Rank Comparison

@Uphartandon , 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://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-Y...

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors