Looking for a end timestamp

[liechpa]

Hi folks

I have the following table with the columns "product", "order_nr", "state_start", "from_state", "to_state" and "state".

I want to calculate the column "state_end" which is always the closest timestamp (depending on the column "product", "order_nr" and "from_state").

If the status is not left, state_end=state_start.

I hope someone can help me

Thanks

[v-frfei-msft · Accepted solution]

Hi @liechpa ,

To create two calculated columns as below.

final date = IF(ISBLANK('Table'[state_end]),'Table'[state_start],'Table'[state_end])

max date = 
CALCULATE (
    MAX ( 'Table'[final date] ),
    FILTER (
        'Table',
        'Table'[Product] = EARLIER ( 'Table'[Product] )
            && 'Table'[order_nr] = EARLIER ( 'Table'[order_nr] )
            && 'Table'[from_state] = EARLIER ( 'Table'[from_state] )
    )
)

[parry2k]

@liechpa sorry not very clear about your question? There is no status column in your sample data. Could you please put it in sample excel file and also the logic to get end_time with explanation, it will help to get the solution