I have below DAX measure:
YoYGrowth =
VAR CY = IF(SELECTEDVALUE(Table_R[Audit_FYear]) = BLANK(), MAX(Table_R[Audit_FYear]), SELECTEDVALUE(Table_R[Audit_FYear]))
VAR PY = CY-1
VAR CurrentYearCount = Calculate([Total Count], FILTER(Table_R, Table_R[Audit_FYear] = CY))
VAR PreviousYearCount = Calculate([Total Count], FILTER(Table_R, Table_R[Audit_FYear] = PY))
VAR YOY_Change = CurrentYearCount - PreviousYearCount
RETURN
YOY_Change
The dax measure works perfectly but in situations where current year does not have values for a specific category, the measure automatically considers latest previous year with no zero value which is not something that i want.
For example:
If Category A has 2024 and 2024 then it will calculate YOY change as value@2024-Value@2023
but if category B does not have value at 2024 or 2023 and has it on 2022 and 2021, then it considers YOY change for that category as Value@2022 – Value@2021. I want it to only consider those values which have 2024 values then then subtract from 2023 values.
Any help is appreciated.