cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver III

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

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
Resolver III

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

Resolver III

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

Resolver III

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

Super User

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

Super User

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.

Visit my LinkedIn page by clicking here.

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors