Forum Discussion
Calculate Duration Across Multiple Dates and Items
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 |
5 Replies
davehusMemorable Member
Hi rsimpson318
Try
Test Measure = CALCULATE(SUM('Table'[DURATION]), ALLEXCEPT('Table','Table'[VEHICLE]))Hope this helpsDid I help you today? Please accept my solution and hit the Kudos button.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.
