Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
rsimpson318
Frequent Visitor

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.

 

DATEVEHICLESTART_DATE_TIMEEND_DATE_TIMEDURATION
8/1/2022116038/1/2022 6:168/1/2022 19:0846270
8/2/2022116038/2/2022 6:358/2/2022 19:5047700
8/3/2022116038/3/2022 6:478/3/2022 19:1144608
8/4/2022116038/4/2022 6:318/4/2022 19:0645293
8/5/2022116038/5/2022 6:328/5/2022 19:0845357
8/8/2022116038/8/2022 6:238/8/2022 19:0945945
8/9/2022116038/9/2022 6:338/9/2022 17:2439030
8/10/2022116038/10/2022 6:248/10/2022 19:0945922
8/11/2022116038/11/2022 6:238/11/2022 19:0645758
8/12/2022116038/12/2022 6:278/12/2022 17:5641346
8/15/2022116038/15/2022 13:368/15/2022 17:5715660
8/16/2022116038/16/2022 6:078/16/2022 15:2333391
8/18/2022116038/18/2022 6:118/18/2022 14:4831056
8/22/2022116038/22/2022 6:178/22/2022 10:5816839
8/23/2022116038/23/2022 6:168/23/2022 18:3044039
8/26/2022116038/26/2022 6:198/26/2022 8:076524
8/1/2022116048/1/2022 6:368/1/2022 19:0845120
8/2/2022116048/2/2022 6:378/2/2022 19:0945126
8/3/2022116048/3/2022 6:378/3/2022 19:1145241
8/4/2022116048/4/2022 6:358/4/2022 19:0645101
8/5/2022116048/5/2022 6:388/5/2022 19:0844967
8/8/2022116048/8/2022 6:378/8/2022 18:5744370
8/9/2022116048/9/2022 6:378/9/2022 19:0544881
8/10/2022116048/10/2022 6:378/10/2022 19:0444835
8/11/2022116048/11/2022 6:398/11/2022 19:0044477
8/12/2022116048/12/2022 6:388/12/2022 19:0644893
8/16/2022116048/16/2022 6:368/16/2022 19:1045215
8/17/2022116048/17/2022 6:388/17/2022 19:1445412
8/18/2022116048/18/2022 6:388/18/2022 19:1045124
8/19/2022116048/19/2022 6:478/19/2022 19:0244104
8/22/2022116048/22/2022 6:368/22/2022 9:3110507
8/23/2022116048/23/2022 6:388/23/2022 19:0144562
8/24/2022116048/24/2022 6:388/24/2022 19:1145223
8/25/2022116048/25/2022 6:378/25/2022 19:0945089
8/26/2022116048/26/2022 6:268/26/2022 19:0645586

 

5 REPLIES 5
thefunmachine3
Frequent Visitor

Did you figure this out? I have a similar problem

davehus
Memorable Member
Memorable Member

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.

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.

@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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.