Forum Discussion

liechpa's avatar
liechpa
Regular Visitor
7 years ago
Solved

Looking for a end timestamp

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

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

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    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

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

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