Dynamic % Difference Dax when filtering on non consecutive years

[chloelgreenwood]

Hi there,

I have a matrix in power BI, items in rows, years in columns and a YOY% difference measure.

I can;t show actual data, so here is an example in excel of what I'm doing:

Problem is I'm not always comparing to previous year. Sometimes we may want to compare 2019 Vs 2022. This works in excel:

Excel calculates the difference in the filtered period. However when I do this in Power BI, when I filter it returns the previous year % difference not filtered period.

In power BI I am using a matrix like this:

Fruit YoY% (from quick measures) code is:

Fruit YoY% =

IF(

    ISFILTERED('Date Table'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_YEAR =

        CALCULATE(

            SUM('Fruit'[Value]),

            DATEADD('Date Table'[Date].[Date], -1, YEAR)

        )

    RETURN

        DIVIDE(SUM(Fruit[Value]) - __PREV_YEAR, __PREV_YEAR)

)

Which works for YoY, but when I filter, eg, 2020 Vs 2022, it still shows 2022 Vs 2021 % dif.

When I use the below code, it works when two filters are on, but defaults to 2019 Vs 2022:

Fruit Earlier = VAR earlieryear = calculate( min ('Date Table'[Year]), allselected ('Date Table')) return calculate ([Total Fruit Sum], all ('Date Table'), 'Date Table'[Year] = earlieryear)

Fruit Later = VAR lateryear = calculate( max ('Date Table'[Year]), allselected ('Date Table')) return calculate ([Total Fruit Sum], all ('Date Table'), 'Date Table'[Year] = lateryear)

fruit % dif = var lateryear = calculate (max('Date Table'[Year]), allselected ('Date Table')) var maxyear = max ('Date Table'[Year]) var earlier_ = [fruit Earlier] return if (lateryear = maxyear, divide ([fruit later]-earlier_ , earlier_))

Ideally, I'd need top level to be YoY%, (across all years if possible but 2022 Vs 2021 would be fine), then the slice the years to get the below:

So ideally it would be: 

1. YoY% difference (2022 Vs 2021, 2021 Vs 2020 etc)

2. Slicer two years (eg 2022 Vs 2020)

3. Filtered years % difference eg 2022 Bs 2020)

4. Remove slicers to return back to YoY% difference (2022 Vs 2021, 2021 Vs 2020 etc)

Any help on what DAX I need to make this work would really help!