Calculating hours worked over x days excluding down time

[KellerB]

Hi! I have a problem that I have been trying to solve for days. I need to calculate working hours by manufacturing line over multiple days. the problem is I have to do this by time of first unit produced-time of last unit produced for each day. I dont know how to go through each day, take the min time and max time, subtract the two, and add to a running total going through everyday that my date slicer has included. If you are able to help that would be awesome!! Let me know

Here is some sample data. This will eventually allow me to calculate an accurate "Units per hour".

so for line 1 it should return (8am-4pm) + (7am-1pm) +(6am-2pm) = 22 hours worked 

I will have a table that has line number as the first column and working hours (this output) as the second column and sliced using a calender...

Line Number	Date	Time	Units
1	9/20/2022	8am	1
1	9/20/2022	10am	1
1	9/20/2022	1pm	1
1	9/20/2022	4pm	1
1	9/21/2022	7am	1
1	9/21/2022	9am	1
1	9/21/2022	11am	1
1	9/21/2022	1pm	1
1	9/22/2022	6am	1
1	9/22/2022	10am	1
1	9/22/2022	2pm	1
2	9/20/2022	8am	1
2	9/20/2022	10am	1
2	9/20/2022	1pm	1
2	9/20/2022	4pm	1
2	9/21/2022	7am	1
2	9/21/2022	9am	1
2	9/21/2022	11am	1
2	9/21/2022	1pm	1
2	9/22/2022	6am	1
3	9/21/2022	10am	1
3	9/21/2022	2pm	1
3	9/22/2022	9am	1
3	9/22/2022	11am	1
3	9/22/2022	1pm	1

[davehus · Accepted solution]

Hi @KellerB ,

I've attached a solution using a summarized table. Let me know if this meets your needs.

Did I help you today? Please accept my solution and hit the Kudos button.

[KellerB]

I just realized my date column contains the day and time so the table creates a new row for everytime. how do i create a new column for just the date? I would like to avoid changing my existing date column as i have measures using it.

[davehus]

Hi @KellerB, Try wrapping the datetime in Date() in the summarize table. 

[KellerB]

Are you able to do a virtual call sometime tomorrow or next week? I feel like i am not good at explaining what is not working haha.

[davehus]

Yes sure. Next week is fine if that suits. Private message me on the community and we'll go from there. 

[KellerB]

Like this? Its giving me an error

[KellerB]

Ok I used .[date] on the end and that seemed to work. However I realized that I am have separate shifts....

Day shift = 5am-6pm

Night Shift = 10pm-6am

I already have in my table what shift each row belongs to so I just need to somehow get it in the summarized table calculation. 

THanks for all your help!

[davehus]

Oddly enough .[Date] didn't work for me when I tried it and I thought it wasn't accepted in a summarize.  😊 if I've helped you, please accept my solution, if not let me know if there is anything else I can do. 

[davehus]

Sorry, my mistake. Are you able to add columns in power query. If so create a custom column with Date.From([DateTimeCol]) call it something like date only and the replace the datetime with the date only column on the summarize table.