I have a set of records of the actions of a manufacturing robot:
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?
@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?
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.
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
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.