Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
jalanhart
Contributor III
Contributor III

Calculate last year's MTD for the whole month

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!

Labels (1)
1 Solution

Accepted Solutions
MayilVahanan

Hi

Try like below


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

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

1 Reply
MayilVahanan

Hi

Try like below


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

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.