cancel
Showing results for
Did you mean:
Super User

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

1 ACCEPTED SOLUTION
Super User

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 6
Super User

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

Super User

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

Super User

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

Solution Supplier

I believe you can use the DATESINPERIOD function, and specify 3 quarters as the time period in your measure. The docs are here: https://learn.microsoft.com/en-us/dax/datesinperiod-function-dax

Community Champion

what about Time Intelligence functions like

Super User

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.

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.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors