Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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?
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
97 | |
85 | |
70 | |
67 |
User | Count |
---|---|
116 | |
109 | |
94 | |
79 | |
72 |