Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Find last changed value and date

Hello,   I have a table with historical data with ID, Status and Date. I would like to add 2 additional columns and can show what is the last "changed value" and last changed date for the same ID. ...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Hello Anonymous 

    Two calculated columns will get what you are looking for I think.

    First we figure out the [Last Changed Date]

    Last Changed Date = 
VAR _Status = 'Table'[Status]
VAR _Date = 'Table'[Date]
RETURN
CALCULATE(
    MAX ( 'Table'[Date] ),
    ALLEXCEPT ( 'Table', 'Table'[ID] ),
    'Table'[Date] < _Date,
    'Table'[Status] <> _Status
)

    Then we can use that to get the [Last Changed Status]

    Last Changed Status = 
VAR _LastChangeDate = 'Table'[Last Changed Date]
RETURN
CALCULATE(
    MAX ( 'Table'[Status] ),
    ALLEXCEPT ( 'Table', 'Table'[ID] ),
    'Table'[Date] = _LastChangeDate
)