Skip to main content
cancel
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.

Reply
chloelgreenwood
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:

chloelgreenwood_0-1681918909609.png

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

chloelgreenwood_1-1681919002050.png

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:

chloelgreenwood_0-1681987422819.png

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.

chloelgreenwood_1-1681987571520.png

 

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_))

 

chloelgreenwood_2-1681988054664.png

 

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:

 

chloelgreenwood_3-1681988114069.png

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! 

0 REPLIES 0

Helpful resources

Announcements
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

PBI_APRIL_CAROUSEL1

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