Year on Year difference without date table

[LUCASM]

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
2023	15956

The formula I have variations of is 

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

RETURN
CALCULATE ( [Total Value],
FILTER ( ALL ( Forecast ),
Forecast[Year] = Prev_Year ) )

but nothing seems to return a result for me I always get an empty result for Previous Year

I am trying to use it in a Matrix like below

Years across the top 

Values are This Year and Difference

2019	2019	2020	2020	2021	2021	2022	2022	2023	2023
Value	Diff PreYr	Value	Diff PreYr	Value	Diff PreYr	Value	Diff PreYr	Value	Diff PreYr
13023		12003	-1020	14967	2962	15534	567	15956	431

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[LUCASM]

Hi @Ashish_Mathur 
I was excited to try your file.
However, although your data works mine does not and I cant see why.
Ive added my test data to your file and renamed my tables and calculations to a suffix2. I am getting zeros everywhere and I cant see why.
Just as a thought, how do you get your calculated Date column with no hierachy?
Ive reattached your file with my data

Variance.pbix 

 

[Ashish_Mathur]

Amend the relationship

[LUCASM]

Schoolboy error, how annoying!

Thank you for your quick responce

[LUCASM]

That my friend is so very helpful.
Love this...

[DataNinja777]

Hi @LUCASM ,

You already have many solutions 😀, but please note that it’s recommended to use a calendar table in your data model for year-on-year calculations. This approach ensures consistent handling of dates and leverages Power BI’s time intelligence functions for greater accuracy. 

Best regards,

[LUCASM]

I agree and in all my reports I have a date table.
In this instance as the data is only yearly data I considered this an overkill as no other calculations could be run over it.

Theres no quarterly, monthly, weekly or daily data available.
Should that change - Im not holding my breath, then of course a data table would be invaluable.
But it is a very wise comment, for which I thank you.

[Bibiano_Geraldo · Accepted solution]

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]

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

Diff YoY = [Total Value] - [Value LY]

Memorable Member | Former Super User If I helped, please accept the solution and give kudos!  Linkedin

[LUCASM]

Thank you for your help

[mh2587]

//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())

[LUCASM]

Thank you .
I especially like the additional text and explanations it is very helpful

[Angith_Nair]

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)