cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DeepDive
Helper IV
Helper IV

How To Calculate First Date of Quarter and End Date of Quarter Basis Max Invoice Date

Dear AllDear All

Dear All, I need Quarter Start Date and Quarter EndDate on the basis of MaxInvoice Date.. Pls provide the Dax function or calculation for same.. thanks

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@DeepDive 

These two measures will convert you [Max InvoiceDate] measure to the start and end of quarter.

Max Date QTR Start = 
DATE ( YEAR ( [Max InvoiceDate] ), ROUNDUP ( DIVIDE ( MONTH ( [Max InvoiceDate] ),3 ),0 ) *3 -2, 1)
Max Date Qtr End = 
EOMONTH ( [Max Date QTR Start],2 )

invoicestrstartend.jpg

View solution in original post

7 REPLIES 7
jdbuchanan71
Super User
Super User

@DeepDive 

These two measures will convert you [Max InvoiceDate] measure to the start and end of quarter.

Max Date QTR Start = 
DATE ( YEAR ( [Max InvoiceDate] ), ROUNDUP ( DIVIDE ( MONTH ( [Max InvoiceDate] ),3 ),0 ) *3 -2, 1)
Max Date Qtr End = 
EOMONTH ( [Max Date QTR Start],2 )

invoicestrstartend.jpg

I logged in just to give this post a like. Marvelous use of logic to come up with that formula. Saved me a few hours of hair pulling too. Thanks!

Now please explain your work for the class. 😉

 

This was a huge help for me, thank you!

Hello @mchughes 

Certainly, it goes like this.  

Given a month number

Divide that by 3

Then round that up to the nearest whole number

Then multiply that by 3

Then subtract 2

This gives us the number of the first month of the quarter.

Month  / 3 Roundup 0 * 3 - 2
1 0.333333333 1 3 1
2 0.666666667 1 3 1
3 1 1 3 1
4 1.333333333 2 6 4
5 1.666666667 2 6 4
6 2 2 6 4
7 2.333333333 3 9 7
8 2.666666667 3 9 7
9 3 3 9 7
10 3.333333333 4 12 10
11 3.666666667 4 12 10
12 4 4 12 10

 

We combine that with the YEAR of the date and DAY 1 and we get the date of the start of the quarter.

Thanks a lot @jdbuchanan71 

VijayP
Super User
Super User

STARTOFQUARTER and ENDOFQUARTER DAX Functions are available in PowerBI now.

Check Them




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Hi, Thanks for your reply. But I want to calculate Start and End date of Quarter on the basis of a Particular date. StartofQuarter and EndofQuarter will not work here.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors