vs LY Measure Over Multiple Years

[Anonymous]

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"

[Mariusz · Accepted solution]

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.

[Anonymous]

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.

[Anonymous]

@Mariusz This did what I was looking for. Thank you again!

[Anonymous]

Thank you @Mariusz ,

This is what I exactly what I needed. Appreciate it!