Year on year variance - calculated measure

[SecretChimpanze]

Hi,

How would I be able to calculate the year on year variance of two lines on a chart? The plan is to show the variance in a separate line chart.

What I currently have is:

X Axis = Launch Week +/- (this is from my date table and Week 0 is launch week. I have around 3 months worth of weeks showing)

Legend = Financial Year (2021 and 2022) also from my date table

Value = Count of Events (from my data table)

What I need is to have a calculated measure that does this --> 2022 data minus 2021 data for whichever week I have showing on my X Axis (this can change depending on the dates selected in a slicer)

[Greg_Deckler · Accepted solution]

@SecretChimpanze Try:

Better Year Over Year Change = 
    VAR __Year = MAX('Table'[Year])
    VAR __Curr = SUMX(FILTER(ALL('Table'),[Year] = __Year),[Value])
    VAR __Prev = SUMX(FILTER(ALL('Table'),[Year] = __Year - 1),[Value])
RETURN
    DIVIDE(__Curr - __Prev, __Prev, 0)

or possibly:

Better Year Over Year Change 2 = 
    VAR __Year = MAX('Table'[Year])
    VAR __EndDate = MAX('Table'[Date])
    VAR __StartDate = MIN('Table'[Date])
    VAR __PrevDateEnd = DATE(YEAR(__EndDate) - 1, MONTH(__EndDate), DAY(__EndDate))
    VAR __PrevDateStart = DATE(YEAR(__StartDate) - 1, MONTH(__StartDate), DAY(__StartDate))
    VAR __Curr = SUMX(FILTER(ALL('Table'),[Date] <= __EndDate && [Date] >= __StartDate),[Value])
    VAR __Prev = SUMX(FILTER(ALL('Table'),[Date] <= __PrevDateEnd && [Date] >= __PrevDateStart),[Value])
RETURN
    DIVIDE(__Curr - __Prev, __Prev, 0)