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

[DarrelDonatto]

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 solution]

Hi @DarrelDonatto ,

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" )
    )

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-chenwuz-msft · Accepted solution]

Hi @DarrelDonatto ,

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" )
    )

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DarrelDonatto · Accepted solution]

This is great.  Thanks so much.  It solves much of what I am trying to accomplish.  Is there a way to get the same measure not in matrix visualization, but so I can use the result to calculate a 90th percentile or an average per unit?