CODEWITHSUNDEEP
dax
Jonathon Brooks
Jonathon Brooks

Reputation: 3

End of last month and last quarter end in DAX

I am trying to work out a a MTD and QTD measures because my data only has YTD. So to get QTD, i need YTD-QTD(-1).

I need to be able to sum everything up to the end of last month. And also sum everything up to the end of last quarter.

E.g. if my report date is 7th May, I need to sum on the date 30th April for MTD (so I can go YTD-YTD(last month). And also need to find the end of last quarter, i.e. 31st March.

I can do the end of month fine:

Fair Value MTD2 = [Fair Value Change YTD]-CALCULATE(Sum(tblPortfolioPerformanceHistorical[profit]),tblPortfolioPerformanceHistorical[reportDate]=EOMONTH(tblPortfolioPerformanceHistorical[reportDate],-1))

How could I do it for quarter end?

I need to equivalently CALCULATE(Sum(tblPortfolioPerformanceHistorical[profit]),tblPortfolioPerformanceHistorical[reportDate]=EOQUARTER(tblPortfolioPerformanceHistorical[reportDate],-1))

Thanks!

I can do the end of month fine:

Fair Value MTD2 = [Fair Value Change YTD]-CALCULATE(Sum(tblPortfolioPerformanceHistorical[profit]),tblPortfolioPerformanceHistorical[reportDate]=EOMONTH(tblPortfolioPerformanceHistorical[reportDate],-1))

How could I do it for quarter end?

I need to equivalently CALCULATE(Sum(tblPortfolioPerformanceHistorical[profit]),tblPortfolioPerformanceHistorical[reportDate]=EOQUARTER(tblPortfolioPerformanceHistorical[reportDate],-1))

Upvotes: 0

Views: 515

Answers (1)

Pieter
Pieter

Reputation: 724

calculate the first day of the next quarter and then subtract a day using maybe PREVIOUSDAY(). Hope you have an explicit calendar table

Upvotes: 0

Related Questions