Forum Discussion
Trouble with SAMEPERIODLASTYEAR function
Hi BH22One ,
After my test, the date in the data you provided is different every year, which is the reason for the blank value. But the calculation performs well in the bar chart.
Or you can customize time intelligence functions like this:
Result = VAR Previous_Year = DATE ( YEAR ( MAX ( 'Table'[DATE] ) ) - 1, MONTH ( MAX ( 'Table'[DATE] ) ), DAY ( MAX ( 'Table'[DATE] ) ) + 1 ) RETURN CALCULATE ( SUM ( 'Table'[UNITS] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[CATEGORY] ), 'Table'[DATE] = Previous_Year ) )Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi BH22One ,
After my test, the date in the data you provided is different every year, which is the reason for the blank value. But the calculation performs well in the bar chart.
Or you can customize time intelligence functions like this:
Result =
VAR Previous_Year =
DATE ( YEAR ( MAX ( 'Table'[DATE] ) ) - 1, MONTH ( MAX ( 'Table'[DATE] ) ), DAY ( MAX ( 'Table'[DATE] ) ) + 1 )
RETURN
CALCULATE (
SUM ( 'Table'[UNITS] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[CATEGORY] ),
'Table'[DATE] = Previous_Year
)
)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
V-lianl-msft Thanks for your input. This solution works perfectly in my report. I did however make a few tweaks to get it to work the way I anticipated. Instead of using a Max Date as a variable, I used the max year - 1 as a variable. This allows me to do a YoY comparison for multiple years.