Measure for time difference between non-consecutive rows?

[WillHarding]

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

[v-rongtiep-msft · Accepted solution]

Hi @WillHarding ,

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

[Whitewater100]

Hello:

Please see solution file attached. https://drive.google.com/file/d/1akPQxeppWYb5ePklqODvgHxcSGXejnGI/view?usp=sharing 

You might want to play around with the time formats. Hope this helps!

[Greg_Deckler]

@WillHarding See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous