topic Re: Return the latest date based on status change in DAX Commands and Tips

Return the latest date based on status change

Anonymous — Wed, 10 Mar 2021 22:26:21 GMT

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

Re: Return the latest date based on status change

amitchandak — Thu, 11 Mar 2021 05:56:36 GMT

Anonymous , with name this measure should work

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

Re: Return the latest date based on status change

jameszhang0805 — Thu, 11 Mar 2021 08:03:56 GMT

Anonymous
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 ) )

Re: Return the latest date based on status change

Anonymous — Thu, 11 Mar 2021 16:47:57 GMT

thank you jameszhang0805 that worked!