Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
CVkara92
New Member

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

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1709964575253.png

 

Jihwan_Kim_1-1709964595423.png

 

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

2 REPLIES 2
CVkara92
New Member

Thank you , it worked

Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1709964575253.png

 

Jihwan_Kim_1-1709964595423.png

 

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.