Forum Discussion

MRoth's avatar
MRoth
Helper I
1 year ago
Solved

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. Bu...
  • Ilgar_Zarbali's avatar
    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.