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. Here is the example:

 

IDStatusDate
AActive9/15/2019
AActive9/18/2019
AInactive9/20/2019
AActive9/21/2019
BActive9/15/2019
BInactive9/18/2019
BActive9/20/2019
BActive9/21/2019
BActive9/25/2019
CInactive9/15/2019
CMaintenance9/18/2019
CInactive9/20/2019
CActive9/21/2019
CInactive9/25/2019


I want to have the result liks this:

IDStatusDateLast changed valueLast Changed Date
AActive9/15/2019  
AActive9/18/2019  
AInactive9/20/2019Active9/18/2019
AActive9/21/2019Inactive9/20/2019
BActive9/15/2019  
BInactive9/18/2019Active9/15/2019
BActive9/20/2019Inactive9/18/2019
BActive9/21/2019Inactive9/18/2019
BActive9/25/2019Inactive9/18/2019
CInactive9/15/2019  
CMaintenance9/18/2019Inactive9/15/2019
CInactive9/20/2019Maintenance9/18/2019
CActive9/21/2019Inactive9/20/2019
CInactive9/25/2019Active9/21/2019

 

Any ideas is appreciated, thank you.

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
)

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    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
)