Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
I modified the suggested code and this worked for me.
Col_second =
IF (
Table1[Finishtime].[Date] <> Table1[Starttime].[Date],
( 24 - HOUR ( Table1[Starttime] )) * 3600
- ( 60 - MINUTE ( Table1[Starttime] ))
- ( 60 - SECOND ( Table1[Starttime] )),
Table1[Duration]
)
Col_second =
IF (
Table1[Finishtime].[Date] <> Table1[Starttime].[Date],
( 24 - HOUR ( Table1[Starttime] )) * 3600
- ( MINUTE ( Table1[Starttime] )) * 60
- ( SECOND ( Table1[Starttime] )),
Table1[Duration]
)
My bad !
This was the solution. Gives me the time in seconds from [starttime] to midnight.
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]
)
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?
I hope you can clarify more.
Thank you very much.
Best.
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
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.
Best.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |