Calculating Working hours excluding downtime and total units and Units per hour.

[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. Additionally I need to do it for specific shifts and count only the units that passed (1). 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 and 6 total units, and 6units/22hours = 0.27 Units per hour. 
I have a summarized table that only considers Line number and just adds whatever number is in column "Passed", but now I need it to consider Line number for each shift and only add Passed units ("1" = YES) to Total units. Let me know if you can help 🙂 

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

[v-yalanwu-msft · Accepted solution]

Hi. @KellerB ;

Try this measure.

Measure = 
SUMX(SUMMARIZE(FILTER(ALL('Table'),[Line Number]=MAX('Table'[Line Number])),[Line Number],[Date],"1",
DATEDIFF( 
    CALCULATE(MIN('Table'[Time]),ALLEXCEPT('Table','Table'[Line Number],'Table'[Date])), 
    CALCULATE(MAX('Table'[Time]),ALLEXCEPT('Table','Table'[Line Number],'Table'[Date])),
    HOUR)),[1])

The final show:

Or try table by dax.

Newtable = SUMMARIZE(FILTER('Table',[Passed?]=1),[Line Number],[Date],
                     "max time",CALCULATE(MAX('Table'[Time]),ALLEXCEPT('Table','Table'[Date],'Table'[Line Number])),
                     "min time",CALCULATE(MIN('Table'[Time]),ALLEXCEPT('Table','Table'[Date],'Table'[Line Number])))

The final show:

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KellerB]

This is what I currently have... I need some kind of if statement to only count Passed units = "1" and do a separate table for day shift and night shift...