Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

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

View solution in original post

6 REPLIES 6
UncleLewis
Resolver III
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

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

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

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

FreemanZ
Super User
Super User

what about Time Intelligence functions like

DATEADD(calendar[date], 9, MONTH)

Jihwan_Kim
Super User
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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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