Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Did you figure this out? I have a similar problem
Hi @rsimpson318
Try
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').
Hi @rsimpson318, replace the Sum(duration) with your Duration measure.
Did I help you today? Please accept my solution and hit the Kudos button.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
84 | |
82 | |
67 | |
49 |
User | Count |
---|---|
135 | |
111 | |
100 | |
65 | |
62 |