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 of using a Switch Function so that if 1st Qtr =1, 2 etc...
I can keep going down that road, might get a little tricky when cross year from Q4 to Q1.
Is there a better approach?

 

Thanks,
-w

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

6 Replies

  • 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.

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    what about Time Intelligence functions like

    DATEADD(calendar[date], 9, MONTH)

  • UncleLewis's avatar
    UncleLewis
    Responsive Resident
    3 years ago

    Thanks Vicky,

    That looks promising.
    First I need the 1ST Quarter for a customer, then 2ND, etc..

    To get 1ST I tried an Offset of 0, assuming that means the current quarter that the Min(Date) is in. However, this is returning a zero?

    Total Net Revenue 1ST Quarter =
    CALCULATE(
    [Total Net Revenue],
    DATESINPERIOD(
    Calendar[Date],
    [First Booking Date],
    0,
    Quarter
    )
    )

    Thanks,




    w

    • UncleLewis's avatar
      UncleLewis
      Responsive Resident
      3 years ago

      I did a bit more testing.

      It seems DatesInPeriod with the Quarter argument, is taking the Min(Date) and then next 91 days.
      That is not what I need.

      Let's say, a customer's min date is 11/1/2021. That date falls in Q4 '21 so I need the Net Revenue for the Customer in Q4 '21. Even though the customer in this instance does not have Revenue prior to 11/1/2021, I need the SUM for Q4 which is 10/1/2021 - 12/31/2011.

      Thanks,
      w

      • UncleLewis's avatar
        UncleLewis
        Responsive Resident
        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