Forum Discussion
Filter to next smaller value (i.e previous version)
Hi,
I have a dataset containing planning versions where I would like to to calulate the variance to the 'previous' version.
In this matrix below, the sequence comes from the dim_version table. But in the visual filters, version 2503 is filtered out.
Using below dax in my measure, version 2504 shows 2503 as its previous version (as it is the next smaller value in dim_versions) , but I want it to apply some kind of dynamic filter that it considers that 2503 is filtered out and displays 2502 as the result.
I can sort of replicate it using visual calculations, but would like a clean & reuasable measure if possible.
You want “previous visible version” (i.e., the next smaller version after all slicers/visual filters are applied).
Use ALLSELECTED (or no ALL at all) so the measure respects what’s currently visible.1) Previous visible version (ID)
Previous Visible Version :=
VAR cur = SELECTEDVALUE ( 'Dim Version'[version] )
RETURN
CALCULATE (
MAX ( 'Dim Version'[version] ),
FILTER (
ALLSELECTED ( 'Dim Version'[version] ), -- keep slicers/visual filters, drop row context
'Dim Version'[version] < cur
)
)- If 2503 is filtered out, for row 2504 this returns 2502.
- If there’s no smaller visible version, it returns BLANK().
Simpler equivalent (often works the same):
Previous Visible Version :=
VAR cur = SELECTEDVALUE('Dim Version'[version])
RETURN CALCULATE( MAX('Dim Version'[version]), KEEPFILTERS('Dim Version'[version] < cur) )(This version relies entirely on current context; if you have cross-highlights affecting context, prefer the ALLSELECTED one.)
2) Variance vs previous visible version
Assume your main measure is [Amount] (sales, qty, etc.).
Variance vs Prev Visible :=
VAR prevVer = [Previous Visible Version]
RETURN
IF (
ISBLANK ( prevVer ),
BLANK (),
[Amount]
- CALCULATE ( [Amount], 'Dim Version'[version] = prevVer )
)3) Tips
- If versions aren’t numeric, replace comparisons with an order column (e.g., 'Dim Version'[Order]) and return the corresponding version via LOOKUPVALUE.
- For ties/duplicates in the version column, ensure there’s a unique order key.
- Want the previous visible value directly (not the ID)? Do:
Prev Visible Amount :=
VAR prevVer = [Previous Visible Version]
RETURN IF ( ISBLANK(prevVer), BLANK(), CALCULATE([Amount], 'Dim Version'[version] = prevVer) )Drop these into your model and your matrix will always step back to the next smaller version that’s still visible.
I hope it will help.
5 Replies
Hi MRoth,
Thank you Ilgar_Zarbali Shahid12523 danextian, for you helpful insights.
I've used the sample data to reproduce your issue and obtained the output below. I've also attached the PBIX file for your reference.
Thank you.
Use this DAX to get the previous version within visual filters:
VAR _Current = SELECTEDVALUE('Dim Version'[version])
RETURN
CALCULATE(
MAX('Dim Version'[version]),
FILTER(
ALLSELECTED('Dim Version'),
'Dim Version'[version] < _Current
)
)
This skips filtered-out versions like 2503 and returns the next smaller visible one—clean, dynamic, and reusable.You want “previous visible version” (i.e., the next smaller version after all slicers/visual filters are applied).
Use ALLSELECTED (or no ALL at all) so the measure respects what’s currently visible.1) Previous visible version (ID)
Previous Visible Version :=
VAR cur = SELECTEDVALUE ( 'Dim Version'[version] )
RETURN
CALCULATE (
MAX ( 'Dim Version'[version] ),
FILTER (
ALLSELECTED ( 'Dim Version'[version] ), -- keep slicers/visual filters, drop row context
'Dim Version'[version] < cur
)
)- If 2503 is filtered out, for row 2504 this returns 2502.
- If there’s no smaller visible version, it returns BLANK().
Simpler equivalent (often works the same):
Previous Visible Version :=
VAR cur = SELECTEDVALUE('Dim Version'[version])
RETURN CALCULATE( MAX('Dim Version'[version]), KEEPFILTERS('Dim Version'[version] < cur) )(This version relies entirely on current context; if you have cross-highlights affecting context, prefer the ALLSELECTED one.)
2) Variance vs previous visible version
Assume your main measure is [Amount] (sales, qty, etc.).
Variance vs Prev Visible :=
VAR prevVer = [Previous Visible Version]
RETURN
IF (
ISBLANK ( prevVer ),
BLANK (),
[Amount]
- CALCULATE ( [Amount], 'Dim Version'[version] = prevVer )
)3) Tips
- If versions aren’t numeric, replace comparisons with an order column (e.g., 'Dim Version'[Order]) and return the corresponding version via LOOKUPVALUE.
- For ties/duplicates in the version column, ensure there’s a unique order key.
- Want the previous visible value directly (not the ID)? Do:
Prev Visible Amount :=
VAR prevVer = [Previous Visible Version]
RETURN IF ( ISBLANK(prevVer), BLANK(), CALCULATE([Amount], 'Dim Version'[version] = prevVer) )Drop these into your model and your matrix will always step back to the next smaller version that’s still visible.
I hope it will help.
Thanks Ilgar_Zarbali for this very comprehensive anwser, includiding how to incorporate the result as a filter into my new measure.
By filtering out I am assuming through a slicer or crossfilters, I would instead just use ALLSELECTED so it considers the visible rows only and not all rows regardless of their visibility in the current context.
