Forum Discussion

UncleLewis's avatar
UncleLewis
Responsive Resident
3 years ago
Solved

DAX Get Next 3 Consecutive Quarters

Hi,   I have a first order date for each customer. From there I can get Quarter and Year. Now I need to get the next three consecutive Quarter/Year from that point. I'm going down the road o...
Need Help
  • UncleLewis's avatar
    UncleLewis
    3 years ago

    Got it.
    Just had to add a new measure to get the first date in the quarter that the customer first date fell in

    First Date In Quarter =
    IF(ISBLANK([First Booking Date]),BLANK(),
    DATE(YEAR(First Booking Date], ROUNDUP( DIVIDE( MONTH( [First Booking Date] ),3 ),0 ) *3 -2 1))

    First Booking Date | First Date In Quarter
    ---------------------------------------------
    5/1/2021                | 4/1/2021

    Thanks,
    -w

Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

One of ways to achieve this is to have something like an offset number column that sorts for quarter-year column in the dim-calendar table.

In order to create this, try using RANKX DAX function that ranks quarter-end date. By using rankx, the consequtive offset numbers are always +1 or -1.

Another way to create this column without using rankx is, creating the offset number column by writing a formula something like, 

 = yearnumber X 4 + quarternumber 

This formula also provides the difference of consequtive offset numbers are always +1 or -1.

 

I hope this helps.