Return the latest date based on status change

[rsreepathi]

Hi,

This is my first post, below is my data and this is what I want to achieve:

for each ID->find the latest status-> corresponding date field

Name	ID	date	status
setting 1	29	3/9/2021 1.42 PM	0
setting 1	29	3/9/2021 1.43 PM	0
setting 1	29	3/9/2021 1.44 PM	0
setting 1	29	3/9/2021 1.45 PM	1
setting 1	29	3/9/2021 1.46 PM	1
setting 1	29	3/9/2021 1.47 PM	1
setting 1	29	3/9/2021 1.48 PM	1
setting 1	29	3/9/2021 1.49 PM	1
setting 2	30	3/9/2021 1.42 PM	0
setting 2	30	3/9/2021 1.43 PM	0
setting 2	30	3/9/2021 1.44 PM	0
setting 2	30	3/9/2021 1.45 PM	1
setting 2	30	3/9/2021 1.46 PM	1
setting 2	30	3/9/2021 1.47 PM	0
setting 2	30	3/9/2021 1.48 PM	0
setting 2	30	3/9/2021 1.49 PM	0
setting 2	30	3/9/2021 1.50 PM	1
setting 2	30	3/9/2021 1.51 PM	0

Based on the above data for setting 1-> the latest state change is from 0 to 1-> date field to return is 3/9/2021 1.45 PM

similarly for setting 2 -> the latest state change is from 1 to 0 -> date field to return is 3/9/2021 1.50 PM

data to extract

setting 1->3/9/2021 1.45PM

setting 2 ->3/9/2021 1.50PM

I am able to get the state to go from either 0 to 1 or 1 to 0, any help would be appreciated.

Thanks 

RS

[rsreepathi]

thank you @jameszhang0805 that worked!

[jameszhang0805 · Accepted solution]

@rsreepathi 
Please try the below code:

LatestDate = 
VAR _Table =
    ADDCOLUMNS (
        SUMMARIZE ( 'Data', Data[Name], Data[date], Data[status] ),
        "@PreviousStatus",
            VAR _PreviousDate =
                CALCULATE (
                    MAX ( 'Data'[date] ),
                    ALL ( 'Data' ),
                    'Data'[date] < EARLIER ( Data[date] )
                )
            VAR _PreviousStatus =
                CALCULATE (
                    MAX ( 'Data'[status] ),
                    ALLEXCEPT ( Data, 'Data'[Name], Data[date] ),
                    'Data'[date] = _PreviousDate
                )
            RETURN
                _PreviousStatus
    )
VAR _FilteredTable =
    FILTER ( _Table, 'Data'[status] <> [@PreviousStatus] && 'Data'[status] = 1 )
RETURN
    IF( HASONEVALUE( Data[Name] ) , CALCULATE ( MAX ( 'Data'[date] ), _FilteredTable ) )       

[amitchandak]

@rsreepathi , with name this measure should work

calculate(max(table[date]), filter(Table, Table[status]=1))