End of last month and last quarter end

[jonnybjb11]

Hi, 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!

[jonnybjb11 · Accepted solution]

Fair Value QTD = [Fair Value Change YTD]-CALCULATE(Sum(tblPortfolioPerformanceHistorical[fairValueChangeAdjForHTM]),tblPortfolioPerformanceHistorical[reportDate]=DATEVALUE(EOMONTH(SELECTEDVALUE(tblPortfolioPerformanceHistorical[reportDate]),-(MOD(MONTH(SELECTEDVALUE(tblPortfolioPerformanceHistorical[reportDate]))-1,3)+1)))) This was the solution

[jonnybjb11]

Expected results are:

YTD	45,483
QTD	8,567.88
MTD	8,567.88

[jonnybjb11]

Hi Stephen, thanks for getting in touch. Some raw data is below. 

So YTD measure is: 

Calculate(Sum(tblPortfolioPerformanceHistorical[profit]),tblPortfolioPerformanceHistorical[reportDate]=MAX(tblPortfolioPerformanceHistorical[reportDate])) and changes as expected with the reportdate. 

I need to make MTD and QTD and because I only have YTD data, it will be, for example, 7/4/2023 YTD - 31/3/2023 for MTD and in this case, same for QTD. But QTD will be different measure because not all month ends are quarter end. Hope this makes sense! Thanks!

Date	Security	Profit YTD
7/4/2023	Security A	231
7/4/2023	Security B	3,872
7/4/2023	Security C	2,121
7/4/2023	Security D	7,583
7/4/2023	Security E	8,528
7/4/2023	Security F	10,559
7/4/2023	Security G	12,589
31/3/2023	Security A	187.48
31/3/2023	Security B	3,142.60
31/3/2023	Security C	1,721.45
31/3/2023	Security D	6,154.53
31/3/2023	Security E	6,921.52
31/3/2023	Security F	8,569.52
31/3/2023	Security G	10,217.51
28/2/2023	Security A	152.17
28/2/2023	Security B	2,550.61
28/2/2023	Security C	1,397.17
28/2/2023	Security D	4,995.16
28/2/2023	Security E	5,617.66
28/2/2023	Security F	6,955.21
28/2/2023	Security G	8,292.76
31/1/2023	Security A	123.50203
31/1/2023	Security B	2070.1293
31/1/2023	Security C	1133.9732
31/1/2023	Security D	4054.1815
31/1/2023	Security E	4559.4171
31/1/2023	Security F	5645.0053
31/1/2023	Security G	6730.5935

[Anonymous]

Hi @jonnybjb11 ,

What's your raw data? If possible, please provide me with some dummy data and expected results.

[Fair Value Change YTD] is a measure, right? Is it YTD obtained with [profit]? [profit] is a column.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.