Calculate last year's MTD for the whole month

[jalanhart]

Hello, 

I need an expression that shows me the previous year's Month To Date sales that ties to the current year's MTD sales, except when it's at the end of the month it needs to show the whole month. 

For example, this year the 29th was the last day of the month of March, but in March 2023 the last work day was the 31st. Using a standard LY MTD calculation is only grabbing sales through 3/29/2023. 

I would like it to show sales from 3/1/2023 - 3/31/2023 to compare to 3/1/2024 - 3/29/2024, and would like it to be automatic as part of a measure and not a a variable input. 

thanks!

[MayilVahanan · Accepted solution]

Hi

Try like below

Sum({<Date={">=$(=Date(MonthStart(Max(Date),-12)))<=$(=Date(Floor(MonthEnd(Max(Date),-12))))"}, Year=, Month=, Week=>}Sales)