Multirow Formula in DAX

[vibhoryadav23]

Hi,

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

(Previous status is desired outcome)

User	Year	Status	Previous Status
A	2019	New
A	2020	Active	New
A	2021	Idle	Active
B	2020	New
B	2021	Deleted	New

Thanks in advance

[Jos_Woolley · Accepted solution]

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

[vibhoryadav23]

Thanks Jos!