Dynamic Ranking Using 'Year/Quarter' String Field

[lr86868]

I am struggling to calculate a measure using the RANKX() function. I would like to create a dynamic ranking measure or column dependendent on my [Year/Quarter] field, which is a text format as the value is the year and Q#. Is there a way to do this without a date field? Below is an example of what I am hoping to accomplish:

For example:

2024 Q3 Rank: 1

2024 Q2 Rank: 2

2024 Q1 Rank: 3

2023 Q4 Rank: 4

and when my data is updated next quarter, I would like the ranks to update:

2024 Q4 Rank: 1

2024 Q3 Rank: 2

2024 Q2 Rank: 3

So on...

[jdbuchanan71 · Accepted solution]

I think you can do it with a couple measure.

The first one calculates an integer value based on the year quarter string.

Int YQ = 
VAR _YQ = SELECTEDVALUE ( 'YourTable'[Year Qtr] )
RETURN 
INT( LEFT ( _YQ,4 ) & RIGHT ( _YQ, 1 ) )

Then you can use that in a RANKX.

Ranking Measure = RANKX ( ALLSELECTED ( 'YourTable'[Year Qtr] ), [Int YQ] )