Forum Discussion

vibhoryadav23's avatar
vibhoryadav23
Helper II
4 years ago
Solved

Multirow Formula in DAX

Hi,

 

How can I compare the values between rows based on the values in other column. Example:

(Previous status is desired outcome)

UserYearStatusPrevious Status

A

2019     New      
A2020     Active     New
A2021     Idle     Active

B

2020     New      

B

2021     Deleted     New

 

Thanks in advance

  • Jos_Woolley's avatar
    Jos_Woolley
    4 years ago

    Hi,

    Calculated Column:

    Previous Status =
VAR This_User = Table1[User]
VAR This_Year = Table1[Year]
VAR Previous_Year =
    CALCULATE(
        MAX( Table1[Year] ),
        FILTER(
            ALL( Table1 ),
            Table1[User] = This_User
                && Table1[Year] < This_Year
        )
    )
RETURN
    LOOKUPVALUE(
        Table1[Status],
        Table1[User], This_User,
        Table1[Year], Previous_Year
    )

    Regards

2 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    Hi,

    Calculated Column:

    Previous Status =
VAR This_User = Table1[User]
VAR This_Year = Table1[Year]
VAR Previous_Year =
    CALCULATE(
        MAX( Table1[Year] ),
        FILTER(
            ALL( Table1 ),
            Table1[User] = This_User
                && Table1[Year] < This_Year
        )
    )
RETURN
    LOOKUPVALUE(
        Table1[Status],
        Table1[User], This_User,
        Table1[Year], Previous_Year
    )

    Regards