Rank rolling 12 months from 1-12 in chronological order

[nbrandborg]

Hi all

I have a dataset containing data from the last 12 months. I would need to compare the earliest month with the latest month. The months will change everytime the dataset is update every month. So I thought it would be best with a supporting column that ranked the months from 1-12 depending on the chronological order. Then I could alway compare month 1 with month 12. This is a snippet of what I want to achieve. I have the first column which is dynamic and I would like to have the second column.

July 2021	1
August 2021	2
September 2021	3
October 2021	4
November 2021	5
December 2021	6
January 2022	7
February 2022	8
March 2022	9
April 2022	10
May 2022	11
June 2022	12

Best regards!

[nbrandborg · Accepted solution]

I found that I had Year and MonthNumber as seperate columns in the dataset. I had to convert the MonthNumber from 1,2,3,4... to 01, 02, 03, 04.... and then concatenate that with the Year. Then I had the column 202107, 202108, 202109, 202110.....
Becuase I had dublicates, so more rows with 202110 for example I used a dense RANKX, which solved it 🙂

[SpartaBI]

@nbrandborg exactly right. In your example you had one row per month and that is why I said "then a simple rank.eq function as a calculated column will suffice" but when this is not the case, then you need the good friend RANKX 🙂

Please don't forget to accept my previous message as a solution for community visibility.
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543

[SpartaBI]

@nbrandborg if you will have a numerical column that represnt these months (now they look like text) then a simple rank.eq function as a calculated column will suffice. It will update everytime with the correct numbers from 1 to 12.
But you need to add before a column like:

Month Year Sorting
202105
202106
.
.

And then you could rank by that

      

Showcase Report – Contoso By SpartaBI