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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

This calculated column formula works

Column = LOOKUPVALUE(Data[Value],Data[Year Month Name],CALCULATE(MAX(Data[Year Month Name]),FILTER(Data,Data[Contact Full Name]=EARLIER(Data[Contact Full Name])&&Data[Year Month Name]<EARLIER(Data[Year Month Name]))),Data[Contact Full Name],Data[Contact Full Name])

Hope this helps.