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.
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.