0

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!

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.