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) User Year Status Previous Status A 2019      New   ...
  • 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

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

  • vibhoryadav23's avatar
    vibhoryadav23
    Helper II
    4 years ago

    Thanks Jos!