Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX getting previous value (multiple columns)

Hi,    I have a final table like this: Year Month Name Account Name Contact Full Name Value Value PM Diff 2021-Jan BT Peter 9   9 2021-Aug BT Peter 8 7,89 0,11 2022-Jan ...
Need Help
Show and Tell
Tips and Tricks
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Anonymous ,

     

    If you need calculated column:

    Column =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Year Month Name] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] < EARLIER ( 'Table'[Year Month Name] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] = _a
        )
    )

    Output:

    If you need measure:

    Measure =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Year Month Name] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] < MAX ( 'Table'[Year Month Name] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] = _a
        )
    )

    Output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-jianboli-msft's avatar
v-jianboli-msft
Community Support
3 years ago

Hi Anonymous ,

 

If you need calculated column:

Column =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Year Month Name] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] < EARLIER ( 'Table'[Year Month Name] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] = _a
        )
    )

Output:

If you need measure:

Measure =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Year Month Name] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] < MAX ( 'Table'[Year Month Name] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] = _a
        )
    )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anvikuttu's avatar
anvikuttu
Advocate I
2 years ago

Hi v-jianboli-msft 

How do we group the above example under "Year Month  Name " field. I mean keep this field and exclude other fields. Is it possible to sum the values shown by Measure without losing the filter context. For eg Aug 2022 should show 9+7= 16 ?