topic Re: Yearly Percent Change with Slicer and gapped years in DAX Commands and Tips

Yearly Percent Change with Slicer and gapped years

lhanson1 — Mon, 23 Oct 2023 13:49:52 GMT

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!

Re: Yearly Percent Change with Slicer and gapped years

FreemanZ — Mon, 23 Oct 2023 14:23:34 GMT

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)

Re: Yearly Percent Change with Slicer and gapped years

lhanson1 — Mon, 23 Oct 2023 14:41:26 GMT

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

Re: Yearly Percent Change with Slicer and gapped years

FreemanZ — Tue, 24 Oct 2023 01:26:29 GMT

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.