Production Data - Throughput Target with various shift times

[farhandsome]

Hi all,

Dealing with a bit of a head scratcher. This is more of a logic based issue rather than actual Power BI code. Hoping someone can help out! Here's the scenario:

Site	Shift Number	Shift Start Time	Shift End Time	Shift Target
Site A	1	8:00 AM	4:00 PM	10000
Site B	1	9:00 AM	6:00 PM	15000
Site B	2	7:00 PM	2:00 AM	6000
Site C	1	6:00 AM	3:00 PM	20000

This table contains the sites as well as their respective shift times. The master table above is part of an effort in order to capture throughput data from each of the following sites. This master table is connected to tables with a running log of output for each site and shift like so:

Site	Shift Number	Output	Timestamp
Site A	1	2500	9:45 AM
Site A	1	4200	11:15 AM
Site A	1	5600	12:37 PM
Site A	1	7500	2:15 PM

So there is a one-to-many relationship between the master table and these child throughput tables. The goal is to create use a gauge chart with the following metrics:

• Value: Latest Throughput Value (Latest Output in Child Table)
• Maximum Value: Throughput Target for the Day (Shift Target in Master Table)
• Target Value: Time-dependent project target
  i.e. if we are halfway through Site A's shift 1, we should be at 5000 units: (time passed in shift / total shift time) * shift output
  if the shift is currently in non-working hours, then the target value = maximum value

Easy enough, but the problem we are facing is the target value erroring for shifts that cross into the next day (i.e. Site B's shift 2).

The shift times are stored as date-independent time values. Here's the code for the measure to get the target value:

Var CurrentTime = HOUR(UTCNOW()) * 60 + MINUTE(UTCNOW())
VAR ShiftStart = HOUR(MAX('mtb MasterTableUTC'[ShiftStartTimeUTC])) * 60 + MINUTE(MAX('mtb MasterTableUTC'[ShiftStartTimeUTC]))
VAR ShiftEnd = HOUR(MAX('mtb MasterTableUTC'[ShiftEndTimeUTC])) * 60 + MINUTE(MAX('mtb MasterTableUTC'[ShiftEndTimeUTC]))
VAR ShiftDiff = ShiftEnd - ShiftStart

Return
IF(CurrentTime > ShiftEnd || CurrentTime < ShiftStart, MAX('mtb MasterTableUTC'[OutputTarget]),  
      ( (CurrentTime - ShiftStart) / ShiftDiff) * MAX('mtb MasterTableUTC'[OutputTarget]))

Basically, if the current time is outside the range of the shift, it should have the target value equal the total shift target, but if it is in the shift time, it calculates it as a ratio of time passed within the shift. This does not work with shifts that cross midnight as the shift end time value is technically earlier than the shift start time value. Any ideas on how to modify the measure to account for these shifts?

[AllisonKennedy]

@farhandsome Are the shift times well defined as only the examples in your table above? It could possibly be done with lots of IF statements (or a SWITCH??) 

Would be easier if you have Date - is there Date columns in both tables that you can combine? Or even if only in the timestamp table, then you can convert the shifts one using dummy dates?

[farhandsome]

@llucasll1 

Hi Allison,

Thank you for the response. Unfortunately, the master table above is just an example - there's over 10 sites with multiple shifts each and that could grow, so I'd need a more modular approach rather than using a host of nested if's.

Could you explain how attaching a date would work? For example, a shift of 6pm to 2am would need to have respective dates of [Today] and [Tomorrow], but when the current time passes midnight, those shift dates would need to change to [Yesterday] and [Today]. Does that make sense?