RankX in DAX to get the correct ranking for month and year - Required Dynamic ranking

[EricShahi]

Can someone help , I need a calcuated measure to return Corrected Rank below by only using year month numeric column. 

Year YearMonthNumeric CorrectedRank
2022 202210 1
2022 202211 2
2022 202212 3
2023 202301 4
2023 202302 5
2023 202303 6
2023 202304 7
2023 202305 8
2023 202306 9
2023 202307 10
2023 202308 11
2023 202309 12

[Ashish_Mathur]

Hi,

Write these measures

YMN = SUM(Data[YearMonthNumeric])

Rank = RANK(DENSE,GENERATE(ALL(Data[YearMonthNumeric]),all(Data[Year])),orderby([YMN],ASC))-1

My rank measure is not optimal.  There should not be a need to subtract 1.  May be someone can identify my mistake and correct the measure.

[EricShahi]

Hey Ashish, thanks for your reply. 

Basically I want to caclaute a ranking based on another ranking column.

e.g. I have this ranking  as a calculated column based on the date, now i want to create a dynamic

measure which does a ranking based on the existing ranking column.... 

Date	Ranking
01/12/2023 00:00	37
01/01/2024 00:00	38
01/02/2024 00:00	39
01/03/2024 00:00	40
01/04/2024 00:00	41
01/05/2024 00:00	42
01/06/2024 00:00	43
01/07/2024 00:00	44
01/08/2024 00:00	45
01/09/2024 00:00	46
01/10/2024 00:00	47
01/11/2024 00:00	48

the table should look like below , 

Date	Ranking	Ranking_II
01/12/2023 00:00	37	1
01/01/2024 00:00	38	2
01/02/2024 00:00	39	3
01/03/2024 00:00	40	4
01/04/2024 00:00	41	5
01/05/2024 00:00	42	6
01/06/2024 00:00	43	7
01/07/2024 00:00	44	8
01/08/2024 00:00	45	9
01/09/2024 00:00	46	10
01/10/2024 00:00	47	11
01/11/2024 00:00	48	12

[Ashish_Mathur]

Please try to apply the solution which i shared with you earlier to this new dataset.  DIY.

[EricShahi]

Your Dax Measure list all rows ranking as 1,  like i said on my previous comment ...I want to create a ranking based on the "Ranking" column below...

[Ashish_Mathur]

Share the download link of the PBI file.

[EricShahi]

@v-weiyan1-msft Please see the above reply and thanks for your help

[EricShahi]

Here is the table , 
below is the measures in DAX : 

YMN = SUM('Table'[YearMonthNumeric])

Rank = RANK(DENSE,GENERATE(ALL('Table'[YearMonthNumeric]),all('Table'[Year])),orderby([YMN],ASC))-1

You can import this as a table 
Year     Date                           Ranking  YearMonthNumeric

2023	01/11/2023 00:00:00	36	202311
2023	01/12/2023 00:00:00	37	202312
2024	01/01/2024 00:00:00	38	202401
2024	01/02/2024 00:00:00	39	202402
2024	01/03/2024 00:00:00	40	202403
2024	01/04/2024 00:00:00	41	202404
2024	01/05/2024 00:00:00	42	202405
2024	01/06/2024 00:00:00	43	202406
2024	01/07/2024 00:00:00	44	202407
2024	01/08/2024 00:00:00	45	202408
2024	01/09/2024 00:00:00	46	202409
2024	01/10/2024 00:00:00	47	202410

I get this , all 1 for rank , 

[Ashish_Mathur · Accepted solution]

PBI file attached.

Hope this helps.

[v-weiyan1-msft]

Hi @EricShahi ,

Based on your description,the data type of the YearMonthNumeric column is numeric.
You might consider using the following code to create Calculated Column.

Corrected Rank = 
RANKX ( ALL ( 'Table' ), 'Table'[YearMonthNumeric],, ASC, DENSE )

Result is as below.

Please correct me if I misunderstood your needs.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EricShahi]