Dynamic Rolling Quarter Rank

[CVkara92]

Would like to understand how dynamic rolling quarter rank written as DAX, I have date table and  want new column as below
and rank should change based on quarter/current year. Max 5 quarter of data can be display in visualization but should kind of dynamic rolling quarter data

QTR	Rank
Q1 2023	5
Q2 2023	4
Q3 2023	3
Q4 2023	2
Q1 2024	1

[CVkara92]

Thank you , it worked

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

WINDOW function (DAX) - DAX | Microsoft Learn

RANK function (DAX) - DAX | Microsoft Learn

KEEPFILTERS function (DAX) - DAX | Microsoft Learn

Sales total: = 
SUM( Sales[sales] )

Recent 5 quarters rank: = 
VAR _today =
    TODAY ()
VAR _t =
    SUMMARIZE (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= _today ),
        'Calendar'[Year-Quarter]
    )
VAR _topfive =
    WINDOW ( 1, ABS, 5, ABS, _t, ORDERBY ( 'Calendar'[Year-Quarter], DESC ) )
RETURN
    RANK ( SKIP, _topfive, ORDERBY ( 'Calendar'[Year-Quarter], DESC ) )

Recent 5 quarters sales: = 
VAR _today =
    TODAY ()
VAR _t =
    SUMMARIZE (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= _today ),
        'Calendar'[Year-Quarter]
    )
VAR _topfive =
    WINDOW ( 1, ABS, 5, ABS, _t, ORDERBY ( 'Calendar'[Year-Quarter], DESC ) )
RETURN
    CALCULATE ( [Sales total:], KEEPFILTERS ( _topfive ) )