cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper IV

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

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

1 ACCEPTED SOLUTION
Super User

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

9 REPLIES 9

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

Frequent Visitor

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
"name","PQ")

Kindly provide a Thumbs ups.

Super User

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

Helper I

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!

Regular Visitor

This was a huge help for me, thank you!

Super User

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.

Helper IV

Thanks a lot @jdbuchanan71

Community Champion

STARTOFQUARTER and ENDOFQUARTER DAX Functions are available in PowerBI now.

Check Them

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Helper IV

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors