Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
JoshuaIdumijie
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 IDStart DateClose DateTimebetweenorders
ABC45112/3/2022 13:4012/6/2022 8:00 
ABC45112/7/2022 9:0012/7/2022 20:004.26
ABC45112/8/2022 7:0012/10/2022 21:003.50
ABC45712/11/2022 15:0012/13/2022 21:305.60
ADE12312/3/2022 13:3012/4/2022 15:30 
ADE12312/7/2022 20:0012/8/2022 15:005.06
AXD35412/4/2022 8:0012/4/2022 18:00 
AXD35412/6/2022 8:0012/6/2022 17:302.40
AXD35412/6/2022 22:0012/7/2022 15:001.29
ERT56612/5/2022 3:0012/8/2022 12:00 
ERT56612/13/2022 6:0012/16/2022 18:0011.63
ERT56612/23/2022 9:3012/25/2022 18:0012.50
2 ACCEPTED SOLUTIONS
grantsamborn
Solution Sage
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

 

 

 

View solution in original post

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)

FreemanZ_0-1673172234020.png

 

View solution in original post

7 REPLIES 7
JoshuaIdumijie
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.

JoshuaIdumijie_2-1673174235205.png

 

JoshuaIdumijie_0-1673173994417.png

JoshuaIdumijie_1-1673174074066.png

 

grantsamborn
Solution Sage
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

 

 

 

JoshuaIdumijie_0-1673169496090.png

 

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?

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 

 

JoshuaIdumijie_0-1673165801401.png

What do you think ?

JoshuaIdumijie_1-1673167090707.png

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. 

 

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)

FreemanZ_0-1673172234020.png

 

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? 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.