cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Applicable88
Post Prodigy
Post Prodigy

When duration exceeds midnight add to next day.

Hello,

 

I have a set of records of the actions of a manufacturing robot:

ActionID Starttime Finishtime Duration
1 1.1.21 13:39 1.1.21 13:45 0:06:00
2 2.13.21 16:00 2.13.21 16:30 0:30:00
3 3.28.21 18:21 3.28.21 19:21 1:00:00
4 5.10.21 20:42 5.10.21 23:42 3:00:00
5 6.22.21 23:03 6.24.21 23:03 48:00:00

I convert the duration time into hours and then I put the duration and the start time into a bar chart to analyse the workload of the machine. 

As you might guess the duration of ActionID number 5 task exceeds the hours that one day has and would display strange values for that day, since the machine cannot worked 48 hours on any day. Depending on the duration of the runtime after midnight,  the runtime should be calculated as the time in the moment it happens. Is there a ways to automatically add the hours exceeding midnight to the correct day of runtime? Or what would be the correct way to put that into a bar chart?

Thank you very much in advance.

Best. 

 

4 REPLIES 4
v-henryk-mstf
Community Support
Community Support

Hi @Applicable88 ,

 

According to your description, when working hours exceed that day, you need to recalculate this part of the duration. Refer to the following formula:

Col_second = 
IF (
    Table1[Finishtime].[Date] <> Table1[Starttime].[Date],
    ( 24 - HOUR ( Table1[Starttime] ) ) * 2400
        + (
            60
                - MINUTE ( Table1[Starttime] ) * 60
                + ( 60 - SECOND ( Table1[Starttime] ) )
        ),
    Table1[Duration]
)

vhenrykmstf_0-1632795833277.png


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-henryk-mstf thank you very much for your effort. Can you please explain to me why you multiply with 2400? If you want to display in seconds shouln't the hours be calculated like this: (24- Starttime ) divide through 3600?

Maybe I wasn't clear, so for exmple  as you see if day 2021-01-01 starts at 13:39:12, it means that a duration of 10:20:48h is accounted for that day and the next day of that same ActionID 16:45h should be accounted. So the workload in percent for 2021-01-01 should be around 45% and for the next day 70%.  Also if the duration is longer than two or three days would the formula allocate the duration to that day where the action is actually happening? 

 

Applicable88_1-1632985950640.png

I hope you can clarify more.

Thank you very much. 

Best. 

 

 

TomMartens
Super User
Super User

Hey @Applicable88 ,

 

please create a pbix file that contains sample data but still reflects your data model (tables, relationships, columns, calculated columns, and measures). Upload the file to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method share the xlsx as well.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens ,

I created a pbix here: 

https://drive.google.com/drive/folders/1rGEpXB_6Ypk5_pm0pxtKRWZ6r6NSePR6?usp=sharing

 

To explain what I did: 

Its a device who automatically work all the orders it got feeded to automatically. Each Action has a start-and a finishtime. The duration are in seconds. As you can see there are two very large bars in the right chart. It's the daily workload of the machine divide to 24hours (in seconds) to see the capacity of that machine. But the data is misleading...

When a machine has a task which is either very long or started just before midnight the workload in % will 

allocate the time to that day when the task had started. Even in the most optimal environment the workload cannot be higher then 100%. It should sum the worktime until midnight on that day day it started and the rest of the working time it should be allocated to the date where the work and task actually happened. 

Applicable88_2-1632617168123.png

Best. 

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!