cancel
Showing results for
Did you mean:
Helper II

## Rank rolling 12 months from 1-12 in chronological order

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!

1 ACCEPTED SOLUTION
Helper II

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 🙂

3 REPLIES 3
Helper II

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 🙂

Community Champion

@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

Community Champion

@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

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors