Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure for time difference between non-consecutive rows?

Hi,

 

I'm fairly new to Power BI so forgive me if this is a common question, I couldn't find anything specific to non-consecutive, variable distance rows. I'm trying to calculate the total delivery time for each truck using the data below as follows: for n deliveries, total route time = Depart(n) - Arrive (1). E.g. for VAN4, total time would be 15:23-10:06 = 5:17.

 

I have no idea how I would go about this. I'm guessing it would be to create a measure that would be filtering by truck first, then find the last in the sequence, and get the time from there; then subtract the earliest in the filtered rows? Even if I was correct in my logic, my knowledge of DAX is very limited at the moment.

 

Truck Name Delivery Sequence Num Arrive Depart
VAN4       1 10:06:00 10:26:00
VAN4       2 11:49:00 12:14:00
VAN4       3 12:45:00 13:00:00
VAN4       4 14:11:00 14:41:00
VAN4       5 15:13:00 15:23:00
VAN3       1 11:11:00 11:36:00
VAN3       2 11:46:00 11:58:00
VAN3       3 17:35:00 17:45:00
VAN2       1 08:26:00 08:38:00
VAN2       2 11:01:00 11:31:00
VAN2       3 12:00:00 12:15:00
VAN2       4 14:44:00 14:59:00
VAN1       1 10:40:00 10:50:00
VAN1       2 13:04:00 13:24:00
VAN1       3 17:31:00 17:43:00
VAN1       4 17:47:00 17:57:00

 

Any help would be greatly appreciated,

Will

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    Please refer to my pbix to see if it helps you.

    Create a measure.

     

    Measure =
VAR _min_arrive =
    CALCULATE (
        MIN ( 'Table'[Arrive] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Truck Name] = SELECTEDVALUE ( 'Table'[Truck Name] )
        )
    )
VAR _max_time =
    CALCULATE (
        MAX ( 'Table'[Depart] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Truck Name] = SELECTEDVALUE ( 'Table'[Truck Name] )
        )
    )
RETURN
    _max_time - _min_arrive

     

    If I have misunderstood your meaning, please provide your desired output and pbix without privacy information.

     

     

     

    Best Regards

    Community Support Team _ Polly

     

3 Replies