cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Regular Visitor

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

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
2 ACCEPTED SOLUTIONS
Solution Sage

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

Super User

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

7 REPLIES 7
Regular Visitor

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.

Solution Sage

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

Regular Visitor

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?

Regular Visitor

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 ?

Regular Visitor

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.

Super User

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

Regular Visitor

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?