Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

Dynamic % Difference Dax when filtering on non consecutive years

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% =
    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."),
            DATEADD('Date Table'[Date].[Date], -1, YEAR)
        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! 


Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors