Forum Discussion
vs LY Measure Over Multiple Years
Hello,
Is there a measure that I can add to the values field to show change over the previous year? I know I can do this with a custom measure for each year, but I would like to see if there's a way without creating a measure for each year.
Essentially, what I'm looking for is 2020 Cases divided by 2019 cases in between "2019 Cases" and "2019 Margin"
Hi Anonymous
Sure, you can use the below.
Measure = DIVIDE( [Sales], --Your measure or aggregation CALCULATE( [Sales], --Your measure or aggregation SAMEPERIODLASTYEAR( 'Calendar'[Date] ) --Date field from date dimension or your calendar table ) )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
5 Replies
- Mariusz
Community Champion
Hi Anonymous
Sure, you can use the below.
Measure = DIVIDE( [Sales], --Your measure or aggregation CALCULATE( [Sales], --Your measure or aggregation SAMEPERIODLASTYEAR( 'Calendar'[Date] ) --Date field from date dimension or your calendar table ) )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.Hello Mariusz,
The response you provided is what I needed, however, I am running into issues where it is not returning either "-100%" for comparison where previous year has a value and current year does not, and the opposite where it should return "100%" when the previous year has no value and current year does have a value.
I know this has to do with division by zero, but haven't been able to correct it yet with either an IF, or IFERROR measure.
Any further suggestions?
- Mariusz
Hi Anonymous
Try something like
Sales Year vs Last Year = VAR _sales = [Sales] RETURN IF( ISBLANK( _sales ), -1, DIVIDE( _sales, _sales - [Sales Last Year] ) )Best Regards,
Mariusz
Please feel free to connect with me.