I'm trying to create a DAX measure in Power BI that calculates the average total number of issues per category across the last 4 custom periods (Versions) which happen irregularly.
I managed to calculate the average over the last 4 Versions, but the issue arises when I use a slicer to select the current Version — the measure only shows values for that selected version, hiding the others. The table I have now:
before selecting the current version after selecting the current version
and the used measure
Avg. Last 4 Refreshes (excluding current refresh) =
VAR AllVersionsSorted =
FILTER(
ALLSELECTED(Errors[Version]),
NOT ISBLANK(Errors[Version])
)
VAR LatestVersions =
TOPN(
4,
ALLSELECTED(Errors[Version]),
Errors[Version],
ASC
)
VAR Top5Versions =
TOPN(5, AllVersionsSorted, Errors[Version], DESC)
VAR Top1Version =
TOPN(1, AllVersionsSorted, Errors[Version], DESC)
VAR FinalVersions =
EXCEPT(Top5Versions, Top1Version)
RETURN
CALCULATE(
DIVIDE([Total Issues], DISTINCTCOUNT(Errors[Category])),
KEEPFILTERS(FinalVersions)
)
Is it possible in DAX to create a measure that always shows data from the last 4 Versions, starting from the one selected in a slicer — even if the slicer filters only the current version?
For example, if I select Version 10, I want to see data for current Version 10 and in another column the last Versions 9, 8, 7 and 6.
My goal: Goal table
Thanks!