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

[DeepDive]

Dear 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

[jgubler]

This function does the hard work behind the scenes. Im guessing it is new:
STARTOFQUARTER(<dates>)

Source:

https://docs.microsoft.com/en-us/dax/startofquarter-function-dax#:~:text=Returns%20the%20first%20dat...

-Joshua

[BI_Power_Guy]

Thanks to @jdbuchanan71 , was able to create dynamic Current Quarter,Previous Quarter all dates logic .if anyone needs it here you go:

define

var _today = date(2022,1,10)
var var1 = {DATE ( YEAR ( TODAY() )-1, 
ROUNDUP ( DIVIDE ( MONTH ( _today),15 ),0 ) *15 -5, 1)}
var var2 = {DATE ( YEAR ( TODAY() ), 
ROUNDUP ( DIVIDE ( MONTH ( _today),6 ),0 ) *6 -5, 1)}

var var3= CALCULATETABLE({
if(MONTH(_today) in {1,2,3},
var1,
var2)
}
)

var var4 = DATE( YEAR (TODAY() ), 
ROUNDUP ( DIVIDE (month(DATE(2022,5,1)),3 ),0 ) *3 -2, 1)-1

EVALUATE
ADDCOLUMNS(CALENDAR(var3,var4),
"name","PQ")

Kindly provide a Thumbs ups.

[jdbuchanan71 · Accepted solution]

@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 )

[ducleanh1]

your sulution is very clever, thanks you

[Tony5]

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!

[mchughes]

Now please explain your work for the class. 😉

This was a huge help for me, thank you!

[jdbuchanan71]

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.

[DeepDive]

Thanks a lot @jdbuchanan71 

[VijayP]

STARTOFQUARTER and ENDOFQUARTER DAX Functions are available in PowerBI now.

Check Them

[DeepDive]

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.