Forum Discussion
Filter to next smaller value (i.e previous version)
- 1 year ago
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.
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.