Forum Discussion
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 ...
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!
//1.Add an Index Column: Since we don’t have dates, add an index to simulate the year ordering. //This can be added in Power Query or DAX as a calculated column in Power BI.
//2.Calculate YoY Difference: Use DAX to create a measure for the YoY Difference, which will //subtract the previous year's sales from the current year's sales.
YoY Difference =
VAR CurrentYearSales = SELECTEDVALUE('Table'[Sales])
VAR PreviousYearSales =
CALCULATE(
SELECTEDVALUE('Table'[Sales]),
'Table'[Index] = EARLIER('Table'[Index]) - 1
)
RETURN
IF(NOT(ISBLANK(PreviousYearSales)), CurrentYearSales - PreviousYearSales)
//3.Calculate YoY Difference %: The YoY Difference % compares the difference relative to the //previous year's sales.
YoY Difference % =
VAR CurrentYearSales = SELECTEDVALUE('Table'[Sales])
VAR PreviousYearSales =
CALCULATE(
SELECTEDVALUE('Table'[Sales]),
'Table'[Index] = EARLIER('Table'[Index]) - 1
)
RETURN
IF(NOT(ISBLANK(PreviousYearSales)), (CurrentYearSales - PreviousYearSales) / PreviousYearSales, BLANK())
Thank you .
I especially like the additional text and explanations it is very helpful
