cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RafaelPassarell
Regular Visitor

Calculate First and Last Day of the previous Quarter

Hi Guys,

 

I am not being able to write two DAX measures that bring me these:

 

Today = 04/25/2019

 

First Date of the Previous Quarter

eg.: 01/01/2019

FirstDayPreviousQuarter = CALCULATE(DATEADD('Date'[Date],-1,QUARTER)) => Got an error. Is that correct?
 

Last Date of the Previous Quarter

eg.: 31/03/2019

Tried this

FirstDayCurrentQuarter = STARTOFQUARTER('Date'[Date]) => result 1/1/2016 (why 2016?)
LastDayPreviousQuarter = [FirstDayCurrentQuarter]-1 => Works, but as the above is not OKAY, this is subtracting 1 from a not okay date. Is there a better way to subtract 1 day from a date?
 
 
Thank you!
1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

@RafaelPassarell seems like you have date dimension in  your model.

 

You can do something like this and add following for last date of prev quarter by changing MIN to MAX

 

First Date of PQ = 
VAR myDate = TODAY()
RETURN
CALCULATE( MIN( 'Date'[Date] ), PREVIOUSQUARTER( CALCULATETABLE(VALUES( 'Date'[Date]), 'Date'[Date] = myDate) ) )





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

2 REPLIES 2
parry2k
Super User III
Super User III

@RafaelPassarell seems like you have date dimension in  your model.

 

You can do something like this and add following for last date of prev quarter by changing MIN to MAX

 

First Date of PQ = 
VAR myDate = TODAY()
RETURN
CALCULATE( MIN( 'Date'[Date] ), PREVIOUSQUARTER( CALCULATETABLE(VALUES( 'Date'[Date]), 'Date'[Date] = myDate) ) )





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Perfect!!

Thank you @parry2k 

 

FirstDayPreviousQuarter =
VAR myDate = TODAY()
RETURN
CALCULATE( MIN( 'Date'[Date] ), PREVIOUSQUARTER( CALCULATETABLE(VALUES( 'Date'[Date]), 'Date'[Date] = myDate) ) )
 
 
LastDayPreviousQuarter =
VAR myDate = TODAY()
RETURN
CALCULATE( MAX( 'Date'[Date] ), PREVIOUSQUARTER( CALCULATETABLE(VALUES( 'Date'[Date]), 'Date'[Date] = myDate) ) )

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors