Calculating the Date/Time difference between two rows (not always consecutive)

Question

I have a table where date/time values are stored within rows by Incident # and Unit.

I am trying to calculate the difference between these values in any given row for an incident number and unit. Note, the date/times in rows are not always consecutive.

Here is a sample of the table:

Data Table
calltime	closecode	unitcode	parent_id	usertyped	timeinsecs	timestamp	descript	transtype
6/1/2022 7:14:42 PM		BC97	2022152110	[Unit Recommendation]	69,307	6/1/2022 7:15:07 PM	Dispatched	D
6/1/2022 7:14:42 PM		E98	2022152110	[Unit Recommendation]	69,307	6/1/2022 7:15:07 PM	Dispatched	D
6/1/2022 7:14:42 PM		R98	2022152110	[Unit Recommendation]	69,307	6/1/2022 7:15:07 PM	Dispatched	D
6/1/2022 7:14:42 PM		R98	2022152110	Entered in Event History - Log Entry.	69,364	6/1/2022 7:16:04 PM	En-Route	E
6/1/2022 7:14:42 PM		E98	2022152110	Mobile Computer Change	69,371	6/1/2022 7:16:11 PM	En-Route	E
6/1/2022 7:14:42 PM		BC97	2022152110	[Enroute Button]	69,384	6/1/2022 7:16:24 PM	En-Route	E
6/1/2022 7:14:42 PM		R98	2022152110	[Arrive Button]	69,463	6/1/2022 7:17:43 PM	Arrived	A
6/1/2022 7:14:42 PM		E98	2022152110	[Arrive Button]	69,464	6/1/2022 7:17:44 PM	Arrived	A
6/1/2022 7:14:42 PM		BC97	2022152110	Mobile Computer Change	69,599	6/1/2022 7:19:59 PM	Arrived	A
6/1/2022 7:14:42 PM		R98	2022152110	R98 T	69,748	6/1/2022 7:22:28 PM	Transport	T
6/1/2022 7:14:42 PM		R98	2022152110	R98 T	69,748	6/1/2022 7:22:28 PM	Beg Mileage	MILE
6/1/2022 7:14:42 PM	BUP	BC97	2022152110	BC97 E98 C BUP	70,074	6/1/2022 7:27:54 PM	Cleared	C
6/1/2022 7:14:42 PM	BUP	E98	2022152110	BC97 E98 C BUP	70,074	6/1/2022 7:27:54 PM	Cleared	C
6/1/2022 7:14:42 PM		R98	2022152110	R98 H	70,120	6/1/2022 7:28:40 PM	At Hospital	H
6/1/2022 7:14:42 PM		R98	2022152110	R98 H	70,120	6/1/2022 7:28:40 PM	End Mileage	MILE
6/1/2022 7:14:42 PM	FIR	R98	2022152110	R98 C FIR	71,892	6/1/2022 7:58:12 PM	Cleared	C

Here is the desired result:

Desired Result
parent_id	unitcode	Dispatched	En-Route	Turnout Time
2022152110	E98	6/1/2022 7:15:07 PM	6/1/2022 7:16:11 PM	01:04

I would appreciate any guidance.

v-chenwuz-msft · Accepted Answer

Hi&nbsp;DarrelDonatto&nbsp;,
&nbsp;
Is this you want?

You can use matrix table visual to do this with a measure like the following:
Turnout Time =
VAR _dis =
    CALCULATE (
        MAX ( 'DateTable'[timestamp] ),
        FILTER ( 'DateTable', [descript] = "dispatched" )
    )
VAR _enr =
    CALCULATE (
        MAX ( 'DateTable'[timestamp] ),
        FILTER ( 'DateTable', [descript] = "en-route" )
    )
RETURN
    IF (
        HASONEVALUE ( DateTable[descript] ),
        FORMAT ( MAX ( 'DateTable'[timestamp] ), "mm/dd/yyyy hh:nn:ss AMPM" ),
        FORMAT ( _dis - _enr, "h:mm:ss" )
    )

&nbsp;
&nbsp;
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Calculating the Date/Time difference between two rows (not always consecutive)

2 Replies

Related content

Dynamic Calculation Different Period

Different Calculations

Calculating difference between consecutive rows

Calculate difference between consecutive rows / columns

Calculating difference between consecutive rows