Forum Discussion

CVkara92's avatar
CVkara92
New Member
2 years ago
Solved

Dynamic Rolling Quarter Rank

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

QTRRank
Q1 20235
Q2 20234
Q3 20233
Q4 20232
Q1 20241

 

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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 ) )

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    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 ) )