Forum Discussion

Helper IV

1 year ago

Solved

Year on Year difference without date table

I have a data set of annual figures I am trying to calculate the Year on Year Difference and Year on Year Difference % Year Sales 2019 13023 2020 12003 2021 14976 2022 15534 ...

Need Help

Bibiano_Geraldo
1 year ago
Hi LUCASM ,
To calculate the Year on Year (YoY) Difference and YoY Difference % without a date table, you can use DAX measures:
Create a measure for the previous year’s value:
Value LY = VAR Prev_Year = MAX(Forecast[Year]) - 1 RETURN CALCULATE( [Total Value], FILTER( ALL(Forecast), Forecast[Year] = Prev_Year ) )
Create a measure for the YoY Difference:
YoY Difference = [Total Value] - [Value LY]
Create a measure for the YoY Difference %:
YoY Difference % = DIVIDE([YoY Difference], [Value LY], 0)
This setup should give you the desired output with the values and their differences year over year.

Thank you!

lkalawski

Resident Rockstar

1 year ago

Hi LUCASM ,

Your variable Prev_Year calculates each year earlier than the maximum in the entire dataset - it does not take into account the selected year.

Please try this measures:

Total Value = 
SUM(Forecast[Sales])

Value LY = 
VAR __StartYear = 
MAX(Forecast[Year]) - 1

VAR __Result =
CALCULATE ( Sum(Forecast[Sales]),
FILTER ( ALL ( Forecast ),
Forecast[Year] = __StartYear ) )

RETURN
    __Result