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

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