Calculate Time between the Parts Produced in Manufacturing Shift

[lotus22]

Any help is appreciated.

With the current shift profile:

(5:00:00 to 16:59:59) day shift (1) and (17:00:00 to 4:59:59) afternoon shift (2)

We need to calculate "Run Time" between the AR in the shift. 

LAST ENTRY – FIRST ENTRY = RUN TIME (MINUTES)

For example, the first entry for AR 40 (Slicer) in Shift 1 (Slicer) on March 15th (slicer)

First entry = 5:25:18

Last entry = 6:15:18

Again

First entry = 17:00:05

Last entry = 17:06:38

I need to calculate the minutes between both instances or add the minutes as many instances happen with the shift. This how that these how many minutes production ran for that particular AR

Date	Time	AR	Cavity	Shift	Status
3/15/2021	4:18:28	40	2	2	1
3/15/2021	5:25:18	40	2	1	1
3/15/2021	6:15:18	40	2	1	1
3/15/2021	6:45:18	67	2	1	1
3/15/2021	7:00:18	67	2	1	1
3/15/2021	7:15:18	40	2	1	1
3/15/2021	17:00:05	40	2	1	1
3/15/2021	17:06:38	40	2	2	1
3/15/2021	18:10:38	40	2	2	1
3/15/2021	18:15:38	40	2	2	1
3/15/2021	19:40:38	67	2	2	1

[DataZoe]

@lotus22 I think you are trying to get to this stage:

I was able to get that by first figuring out the "Run Index" then determining the difference in time (I averaged it here in the total) between the start and end times.

In Power Query,

1. I sorted by AR, Date, Time and then added an index. 
2. Referenced the table, and kept only the AR and Index columns.
3. I then grouped by AR and kept only the minimum of the index (the first start). I did this to determine if the runs start on an odd or even number.

I import it, then created these calculated columns:

1. Run Start Odd or Even =
   if(MOD(LOOKUPVALUE('Table Starts'[StartIndex],'Table Starts'[AR],'Table'[AR]),2)=1,"Odd","Even")
2. Run Index = if('Table'[Run Start Odd or Even]="Odd", if(mod('Table'[Index],2)=0,'Table'[Index]-1,'Table'[Index]))

Then finally these measures:

1. Run Time (mins) = averagex(SUMMARIZE('Table','Table'[AR],'Table'[Run Index]),CALCULATE(DATEDIFF(MIN('Table'[Time]),max('Table'[Time]),MINUTE)))
2. Run Interval = minx(SUMMARIZE('Table','Table'[AR],'Table'[Run Index]),format(MIN('Table'[Time]),"hh:mm:ss") & "-" & format( max('Table'[Time]),"hh:mm:ss"))

Hope this helps!

[lotus22]

Hi DataZoe, Thank you for your answer. Another issue is that Shift 2 wraps into the next day

(5:00:00 to 16:59:59) day shift (1) and (17:00:00 to 4:59:59) afternoon shift (2)

I am not sure if your solution will take care of that as well.  Production would like to know truly how many minutes we build the Parts in the first and second shifts.

[DataZoe · Accepted solution]

@lotus22 at in that case, I would combine the date and time into a single DateTime in PowerQuery and then use that instead of just the time piece. The key piece is to identify the runs (which I do by sorting by AR then DateTime, then adding index, figuring out if it starts on odd or even number, then marking each run pair) then from that it's simple to calculate the time between when it starts and when it stops.