Calculate Duration Across Multiple Dates and Items

[rsimpson318]

Hello - 

Sorry if this has been asked before: I have a table that includes the date, vehicle, and time that it received a response. I've created a table that displays the vehicle, date, and start time (min datetime) and end time (max datetime) for each day. I'm trying to figure out how to calculate the total duration for a vehicle over multiple days. There are also multiple vehicles, so it would need to display the duration per vehicle as well.

I've added some data below. In the example I've provided, the total duration would be 594,738 for vehicle 11603 and 819,833 for vehicle 11604. When I try to make a matrix, it takes the difference of the min datetime and max datetime irregardless of the date.

DATE	VEHICLE	START_DATE_TIME	END_DATE_TIME	DURATION
8/1/2022	11603	8/1/2022 6:16	8/1/2022 19:08	46270
8/2/2022	11603	8/2/2022 6:35	8/2/2022 19:50	47700
8/3/2022	11603	8/3/2022 6:47	8/3/2022 19:11	44608
8/4/2022	11603	8/4/2022 6:31	8/4/2022 19:06	45293
8/5/2022	11603	8/5/2022 6:32	8/5/2022 19:08	45357
8/8/2022	11603	8/8/2022 6:23	8/8/2022 19:09	45945
8/9/2022	11603	8/9/2022 6:33	8/9/2022 17:24	39030
8/10/2022	11603	8/10/2022 6:24	8/10/2022 19:09	45922
8/11/2022	11603	8/11/2022 6:23	8/11/2022 19:06	45758
8/12/2022	11603	8/12/2022 6:27	8/12/2022 17:56	41346
8/15/2022	11603	8/15/2022 13:36	8/15/2022 17:57	15660
8/16/2022	11603	8/16/2022 6:07	8/16/2022 15:23	33391
8/18/2022	11603	8/18/2022 6:11	8/18/2022 14:48	31056
8/22/2022	11603	8/22/2022 6:17	8/22/2022 10:58	16839
8/23/2022	11603	8/23/2022 6:16	8/23/2022 18:30	44039
8/26/2022	11603	8/26/2022 6:19	8/26/2022 8:07	6524
8/1/2022	11604	8/1/2022 6:36	8/1/2022 19:08	45120
8/2/2022	11604	8/2/2022 6:37	8/2/2022 19:09	45126
8/3/2022	11604	8/3/2022 6:37	8/3/2022 19:11	45241
8/4/2022	11604	8/4/2022 6:35	8/4/2022 19:06	45101
8/5/2022	11604	8/5/2022 6:38	8/5/2022 19:08	44967
8/8/2022	11604	8/8/2022 6:37	8/8/2022 18:57	44370
8/9/2022	11604	8/9/2022 6:37	8/9/2022 19:05	44881
8/10/2022	11604	8/10/2022 6:37	8/10/2022 19:04	44835
8/11/2022	11604	8/11/2022 6:39	8/11/2022 19:00	44477
8/12/2022	11604	8/12/2022 6:38	8/12/2022 19:06	44893
8/16/2022	11604	8/16/2022 6:36	8/16/2022 19:10	45215
8/17/2022	11604	8/17/2022 6:38	8/17/2022 19:14	45412
8/18/2022	11604	8/18/2022 6:38	8/18/2022 19:10	45124
8/19/2022	11604	8/19/2022 6:47	8/19/2022 19:02	44104
8/22/2022	11604	8/22/2022 6:36	8/22/2022 9:31	10507
8/23/2022	11604	8/23/2022 6:38	8/23/2022 19:01	44562
8/24/2022	11604	8/24/2022 6:38	8/24/2022 19:11	45223
8/25/2022	11604	8/25/2022 6:37	8/25/2022 19:09	45089
8/26/2022	11604	8/26/2022 6:26	8/26/2022 19:06	45586

[Anonymous]

Did you figure this out? I have a similar problem

[davehus]

Hi @rsimpson318 

Try 

Test Measure = CALCULATE(SUM('Table'[DURATION]), ALLEXCEPT('Table','Table'[VEHICLE]))

Hope this helps

Did I help you today? Please accept my solution and hit the Kudos button.

[rsimpson318]

When I try to add the measure, I get the message that the column 'DURATION' cannot be found or may not be used in this expression.

It may be helpful to note that 'DURATION' is a measure of 'START_DATE_TIME' - 'END_DATE_TIME' and 'START_DATE_TIME' is a measure of MIN('DATE_TIME') while 'END_DATE_TIME' is a measure of MAX('DATE_TIME').

[davehus]

Hi @rsimpson318, replace the Sum(duration) with your Duration measure.

Did I help you today? Please accept my solution and hit the Kudos button.

[rsimpson318]

@davehus 

It didn't give an error this time, but it still calculated the duration as the first date for the vehicle and the last day instead of the sum for the difference of each day. So in the table above, it's calculating the duration for vehicle 11603 as the difference between 08/01/22 06:16:56 AM and 8/26/22 08:07:46 AM instead of adding up all the values.