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.
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.
- MRoth1 year agoHelper I
Thanks Ilgar_Zarbali for this very comprehensive anwser, includiding how to incorporate the result as a filter into my new measure.