Year on Year difference without date table

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)

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

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

Hi,

PBI file attached.

Hope this helps.

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,