topic Re: Measure for time difference between non-consecutive rows? in DAX Commands and Tips

Measure for time difference between non-consecutive rows?

Anonymous — Mon, 21 Feb 2022 15:00:42 GMT

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

Re: Measure for time difference between non-consecutive rows?

Greg_Deckler — Mon, 21 Feb 2022 15:37:07 GMT

Anonymous 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/339586.
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

Re: Measure for time difference between non-consecutive rows?

Whitewater100 — Mon, 21 Feb 2022 15:37:21 GMT

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!

Re: Measure for time difference between non-consecutive rows?

Anonymous — Thu, 24 Feb 2022 05:14:01 GMT

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