How to calculate the duration between when a truck completes an order to when another order starts

[JoshuaIdumijie]

Please how can I achieve this using Power BI. 

I have a data with several truck IDs and each truck entry of when it started a trip and when it ended that trip and then started another trip. 

I need to calculate the duration between when a truck trip ended and when it's next trip started.

Truck ID	Start Date	Close Date	Timebetweenorders
ABC451	12/3/2022 13:40	12/6/2022 8:00
ABC451	12/7/2022 9:00	12/7/2022 20:00	4.26
ABC451	12/8/2022 7:00	12/10/2022 21:00	3.50
ABC457	12/11/2022 15:00	12/13/2022 21:30	5.60
ADE123	12/3/2022 13:30	12/4/2022 15:30
ADE123	12/7/2022 20:00	12/8/2022 15:00	5.06
AXD354	12/4/2022 8:00	12/4/2022 18:00
AXD354	12/6/2022 8:00	12/6/2022 17:30	2.40
AXD354	12/6/2022 22:00	12/7/2022 15:00	1.29
ERT566	12/5/2022 3:00	12/8/2022 12:00
ERT566	12/13/2022 6:00	12/16/2022 18:00	11.63
ERT566	12/23/2022 9:30	12/25/2022 18:00	12.50

[grantsamborn · Accepted solution]

Would this work as a measure?

zMeasure = 
VAR _ID = SELECTEDVALUE( 'Trips'[Truck ID] )
VAR _StartDate = MAX( 'Trips'[Start Date] )
VAR _PrevEndDate =
    CALCULATE(
        MAX( 'Trips'[Close Date] ),
        FILTER(
            ALL( 'Trips' ),
            'Trips'[Truck ID] = _ID
                && 'Trips'[Close Date] < _StartDate
        )
    )
VAR _Diff = DATEDIFF( _PrevEndDate, _StartDate, HOUR )
RETURN
    _Diff

https://1drv.ms/u/s!AnF6rI36HAVkhPIWg6Fie-7JBEQkew?e=7tkpZy

[JoshuaIdumijie]

Both FreemanZ and GrantsAmborn approach were correct and I haven't been able to figure out why mine doesn't display the blanks. Initially I though it was an error but upon closer examination I realised I for example Truck ABC451 had 3 entries in the main data but in the visual it had only 2 which makes sense too and in the data tab it shows correctly . 

Thank you both so so much I am super grateful.

[grantsamborn · Accepted solution]

Would this work as a measure?

zMeasure = 
VAR _ID = SELECTEDVALUE( 'Trips'[Truck ID] )
VAR _StartDate = MAX( 'Trips'[Start Date] )
VAR _PrevEndDate =
    CALCULATE(
        MAX( 'Trips'[Close Date] ),
        FILTER(
            ALL( 'Trips' ),
            'Trips'[Truck ID] = _ID
                && 'Trips'[Close Date] < _StartDate
        )
    )
VAR _Diff = DATEDIFF( _PrevEndDate, _StartDate, HOUR )
RETURN
    _Diff

https://1drv.ms/u/s!AnF6rI36HAVkhPIWg6Fie-7JBEQkew?e=7tkpZy

[JoshuaIdumijie]

Apologies for the mix up this is correct based on how the measure worked in a table which is exactly what i need but those rows with a dot beside should have returned no values as they were no earlier trip in the data set and hence there was nothing to compare the first trip for a truck in the data. 

So, I JUST CHECKED THE FILE YOU SENT AND IT WORKS PERFECTLY BUT I AM NOT SURE WHY IT'S NOT WORKING FOR MY DATASET, I EVEN RENAMED COLUMNS TO YOURS AND NOTHING YET, WHAT DO YOU THINK COULD BE WRONG?

[JoshuaIdumijie]

Thank you so much, it worked to an extent. Now assuming a truck was entered 5 types that would imply that we would only between able to calculate the difference between 4 trips, so it will be expected that one row will always be empty until another row for that truck is added and then that observation is going to be empty 

What do you think ?

[JoshuaIdumijie]

Apologies for the mix up this is correct based on how the measure worked in a table which is exactly what i need but those rows with a dot beside should have returned no values as they were no earlier trip in the data set and hence there was nothing to compare the first trip for a truck in the data. 

[FreemanZ · Accepted solution]

hi @JoshuaIdumijie 

Not sure if i fully get you, you may try to add a column like this:

TripDuration = 
VAR _id =[Truck ID]
VAR _start = [Start Date]
VAR _table = FILTER(data, data[Truck ID] = _id)
VAR _closepre =
MAXX( 
    FILTER(_table, data[Start Date]<_start),
    data[Close Date]
)
VAR _duration = DATEDIFF(_closepre, [Start Date], HOUR)
RETURN
IF(_closepre=BLANK(), BLANK(), _duration)

[JoshuaIdumijie]

Thank you so much FreemanZ your approach worked correctly like grantsamborn's approach of using a measure but for some strange reasons mine doesn't display the blanks. Any idea why it is not displaying the blanks?