Forum Discussion

LUCASM's avatar
LUCASM
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's avatar
    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!

Angith_Nair's avatar
Angith_Nair
Continued Contributor
1 year ago

Hi LUCASM 

Try to create multiple measures as shown below.

Measure for Previous Year's Value:

Value LY = 
CALCULATE(
    SUM(Forecast[Sales]),
    FILTER(
        ALL(Forecast),
        Forecast[Year] = MAX(Forecast[Year]) - 1
    )
)

Measure for YoY Difference:

YoY Difference = 
SUM(Forecast[Sales]) - [Value LY]

Measure for YoY Difference Percentage:

YoY Difference % = 
DIVIDE([YoY Difference], [Value LY], 0)