Yearly Percent Change with Slicer and gapped years

[lhanson1]

Hi - 

I'm trying to calculate the percent change in a count of items labelled as "major" in a dataset between two different years. However, I don't have all consecutive years, just 2020, 2022 and 2023. This was causing issues for my formula and I've tried a few different approaches but still don't have a final solution. My current formula, pasted below, returns for all my variables except PrevYearCount, and I can't figure out why. It also returns 0 when 2020 is selected but is blank when 2022 or 2023 is selected.

% Change in Majors =

VAR CurrYear = SELECTEDVALUE('SIA Interact'[Year]) // Year slicer input

VAR CompAmount = IF(CurrYear = 2020, 0, IF(CurrYear = 2022, 2, 1)) // To account for missing years/gaps

VAR PrevYear = CurrYear - CompAmount

VAR CurrYearCount = CALCULATE

    (DISTINCTCOUNT('SIA Interact'[Finding Title]),

    Filter('SIA Interact', 'SIA Interact'[Year]= CurrYear), 'SIA Interact'[Finding Rating]= "Major"

)

VAR PrevYearCount = CALCULATE // This is where something breaks down - this var doesn't return

    (DISTINCTCOUNT('SIA Interact'[Finding Title]),

    Filter('SIA Interact', 'SIA Interact'[Year]= PrevYear), 'SIA Interact'[Finding Rating]= "Major"

)

RETURN

    DIVIDE(CurrYearCount - PrevYearCount, PrevYearCount)

Any help would be greatly, greatly appreciated. I've been trying to get this for a few days now and haven't had any luck, I'm relatively new to PowerBI and DAX and I know I could just manually calculate this for the 2 years it matters but I want to be able to have it automated so when the dataset is updated over the years it will still work. Thank you!

[FreemanZ · Accepted solution]

hi @lhanson1 

try like:

% Change in Majors =

VAR CurrYear = SELECTEDVALUE('SIA Interact'[Year]) // Year slicer input

VAR CompAmount = IF(CurrYear = 2020, 0, IF(CurrYear = 2022, 2, 1)) // To account for missing years/gaps

VAR PrevYear = CurrYear - CompAmount

 

VAR CurrYearCount = CALCULATE

    (DISTINCTCOUNT('SIA Interact'[Finding Title]),

    Filter('SIA Interact', 'SIA Interact'[Year]= CurrYear), 'SIA Interact'[Finding Rating]= "Major"

)

VAR PrevYearCount = CALCULATE // This is where something breaks down - this var doesn't return

    (DISTINCTCOUNT('SIA Interact'[Finding Title]),

    Filter(ALL('SIA Interact'), 'SIA Interact'[Year]= PrevYear), 'SIA Interact'[Finding Rating]= "Major"

)

RETURN

    DIVIDE(CurrYearCount - PrevYearCount, PrevYearCount)

[lhanson1]

This seems to work, thank you so so much! Why is the All() necessary, and only in one variable?

[FreemanZ]

hi @lhanson1 

Your year selection itself is a kind of filter context and ALL() is to remove such filter context.
Otherwise, it calculates something when year = 2020 and year = 2022, which returns blank.