Build a Shift Calendar for Textile Production Dashboards

A sewing event at 00:15 has a calendar date. It may still belong to the previous production shift. The difference can move output between days and distort an overnight target. A supervisor's report may then disagree with the ERP.

A reliable shift calendar gives every event a defined plant, time basis, and shift instance. It also handles events that do not fit the approved schedule. The aim is to make the assignment repeatable in Excel, SQL, and Power BI.

This guide builds an original calendar for a hypothetical textile plant that uses UTC+06:00 throughout the example period. It explains midnight boundaries, exceptional shifts, late records, and events that span two shifts. The schedule and quantities are illustrative. They are not a recommendation about working hours or labour arrangements.

Separate three meanings of date

Keep the event instant, local calendar date, and production date as separate fields.

The event instant identifies when something happened on a common time basis. The local calendar date is the date shown by the plant's clock. The production date is the label that management assigns to a shift or production period.

For example, an event at 00:15 on 28 October can belong to the night shift that began on 27 October. Its local calendar date is 28 October. Under a shift-start naming rule, its production date is 27 October.

Neither date is inherently wrong. The error occurs when one report uses calendar date and another uses production date without saying so.

Give every date field a clear name. “Date” is too vague for a shared model. Use names such as EventDateLocal, ShiftProductionDate, and ReceivedDateUtc. Explain which one controls the main dashboard filter.

Establish the clock and source meaning

Ask each source owner what its timestamp represents. A machine may send the time of the event. A barcode application may send the time a local queue created the record. An interface may add the time the server received it.

Keep those timestamps separate. A delayed upload should not automatically move yesterday's output into today's shift.

Preserve the raw source timestamp and its stated offset or zone. Keep the normalised UTC instant too. Add the time the warehouse received it. Retain the device or source identifier so a clock problem can be traced.

A timestamp such as “28/10/2026 00:15” has no offset. The system needs a documented rule to interpret it. Do not infer that rule from the laptop on which the report happens to refresh.

Power Query's local-time functions can behave differently in Desktop and online. Microsoft documents that difference. Online evaluation can return UTC where a developer expected the plant's local time. Power Query time-function guidance

Define each shift as an interval

For this example, the plant uses three shifts:

Treat each interval as including its start and excluding its end. In mathematical notation, that is a half-open interval.

An event belongs to a shift when StartUtc ≤ EventTimeUtc < EndUtc.

The event at exactly 06:00 belongs to the new day shift. It does not also belong to the night shift ending at 06:00.

This convention avoids double assignment at a shared boundary. It also makes adjacent shifts fit together without inventing a gap of one second or one millisecond.

Store full start and end timestamps. A night shift's end is on the next calendar day. Comparing only time-of-day values loses that relationship.

Give every shift instance its own key

“Night shift” is a recurring pattern. A specific night shift at one plant is an instance of that pattern.

Build one calendar row per plant and shift instance. Give the record a ShiftKey, PlantID, and ShiftCode. Add its ProductionDate. Store StartLocal and EndLocal beside StartUtc and EndUtc. Use CalendarVersion to identify the schedule version.

The displayed key is readable for this example. A production database may use a numeric surrogate key and retain the readable label separately.

Do not reuse the same key for every night shift. A fact record needs to identify one instance, so later changes can be checked against the exact schedule used.

Convert the example to UTC explicitly

The example plant uses a fixed offset of six hours ahead of UTC during the period shown.

UTC instant = local timestamp − 6 hours.

Therefore, the night shift beginning at 22:00 on 27 October starts at 16:00 UTC on that date. It ends at 00:00 UTC on 28 October.

An event at 00:15 local on 28 October is 18:15 UTC on 27 October. It lies inside the night shift's UTC interval and receives production date 27 October.

A separate event received as 23:40 UTC on 27 October converts to 05:40 local on 28 October. If 23:40 is its actual event instant, it belongs to the same night shift.

Power Query's DateTimeZone.SwitchZone changes the offset of a value that already has a timezone component. The input must meet the documented rules. A timezone-free value cannot stand in for a properly zoned instant. SwitchZone reference

Use named zones where offsets change

A constant offset is suitable only when it matches the plant and period being modelled. Do not apply the example's six-hour subtraction to every site.

Some locations change offset during the year. A local time can be skipped when clocks move forward or repeated when they move back. A plain local timestamp may then be invalid or ambiguous.

SQL Server's AT TIME ZONE uses time-zone rules and documents specific handling for gaps and overlaps. It also notes that those rules can change outside the database engine. AT TIME ZONE documentation

For each shift instance, retain the named zone and resolved offset. Keep its UTC boundaries too. This matters across countries with different time rules. Decide how a source must identify repeated local times. Prefer an unambiguous instant from the source.

A converted value still needs review. The database producing a result does not prove the source time was valid. If a source reports a nonexistent local time, preserve that fact and investigate the device or interface.

The interval's real duration should be calculated from its UTC instants. Subtracting two clock labels can give the wrong elapsed time around an offset change.

Assign events using the interval table

The assignment needs the event's plant and normalised UTC timestamp. A conceptual SQL join is:

SELECT
e.EventID,
e.PlantID,
e.EventTimeUtc,
s.ShiftKey,
s.ProductionDate
FROM ProductionEvent AS e
LEFT JOIN ShiftCalendar AS s
ON e.PlantID = s.PlantID
AND e.EventTimeUtc >= s.StartUtc
AND e.EventTimeUtc < s.EndUtc;

This is an illustrative pattern. Adapt types, keys, indexes, and names to the database, then test the query with the expected records.

A left join preserves unmatched events for review. An inner join can make those events disappear from the reporting dataset, hiding a calendar gap or a timestamp fault.

Check the joined output before accepting it. Count the matched ShiftKeys for every EventID. An event within a scheduled production period should match once.

Zero matches need an explicit status. More than one match indicates overlapping calendar rows or another join error. Neither case should be silently converted into a normal shift assignment.

Prove the boundary with a small dataset

Use exact-boundary tests before loading a large transaction table.

The correct total is:

Source pieces = 70 + 80 + 90 = 240 pieces.

With an inclusive end condition, event B can join to both N and D. Event C can join to both D and E. The joined total becomes:

Incorrect joined pieces = 70 + 80 + 80 + 90 + 90 = 410 pieces.

Duplicate contribution = 410 − 240 = 170 pieces.

The source rows are not duplicated. The join duplicates their contribution. A “remove duplicate EventID” step after the join would hide the fault while leaving an arbitrary shift assignment.

Fix the interval rules or calendar rows instead. Then confirm both total quantity and exact shift membership.

Preserve output outside the normal schedule

A factory can have authorised overtime, a trial run, maintenance activity, or a wrongly dated event outside normal shifts. These cases need different treatment.

Keep the raw event and an assignment status such as Matched, NoScheduledShift, or MultipleMatches. Ask the calendar owner to review the exception. Is an approved extra interval missing? Does the timestamp need correction?

Do not automatically attach every unmatched event to the closest shift. A nearest-time rule might attach an evening trial to a morning shift. That assignment may have no valid basis.

If the plant requires an “unscheduled production” reporting bucket, define it explicitly. Show the quantity and source records so managers can distinguish legitimate extra work from a data problem.

Use ApparelDiary's WIP reconciliation guide to reconcile the movement quantities separately. A valid quantity balance does not prove that every movement has the right shift label.

Keep breaks separate from event ownership

A break affects available working time, but it need not create a hole in the shift's ownership interval.

Suppose a day shift runs from 06:00 to 14:00 with a 30-minute planned break. Its elapsed duration is 480 minutes, and its scheduled net working time in this example is 450 minutes.

Net scheduled minutes = 480 minutes − 30 minutes = 450 minutes.

Keep the shift interval intact for assigning events. Store break intervals or planned working segments in a separate structure used for time calculations.

An event during a planned break may be valid, delayed, or a sign of a clock error. It still needs a traceable owner. Deleting the break period from the assignment calendar would turn it into an unexplained unmatched record.

Production-resource calendars may incorporate shifts, holidays, absences, and capacity. Microsoft's shop calendar guidance illustrates that planning context.

Keep resource availability separate from event ownership. They answer different questions in the report.

Split duration events by their overlap

A point event, such as a completed bundle scan, receives one shift. A duration event, such as a machine stop, can cross a boundary.

Consider a stop from 05:50 to 06:20. Ten minutes overlap the night shift and twenty minutes overlap the day shift. The sum must remain the original thirty minutes.

For any shift interval, calculate:

Overlap = max(0, min(EventEnd, ShiftEnd) − max(EventStart, ShiftStart)).

All four timestamps must use the same instant basis. Express the result in a stated unit, such as minutes.

Store the original stop identifier on each allocated segment. Then a reviewer can see that two rows represent one physical stop, not two failures.

Do not split a point quantity by this duration formula. A 200-piece bundle scanned at 06:05 may represent work done earlier, but its scan time does not reveal how many pieces were completed in each shift. That requires a different capture rule or supporting record.

Handle open intervals without guessing their end

A machine stop may still be open when the dashboard refreshes. A duration calculation needs a defined observation cutoff.

For example, report “elapsed stop time through 06:10” rather than treating the current time as the final end. Keep the event open and recompute the observed overlap when new evidence arrives.

Use one consistent cutoff for a refresh run. Otherwise, each row may use a slightly different time. The totals can then be hard to reproduce.

Power Query offers fixed UTC time functions that retain one value during a query evaluation. That can support a documented observation cutoff. It does not replace the real event end when the source later provides it. Fixed and dynamic UTC functions

Record the cutoff with the resulting snapshot. A manager viewing the report later should know whether an open stop duration is current to 06:10 or 06:25.

Do not count the temporary cutoff as evidence that the machine restarted.

Version exceptional calendars

A normal weekly pattern is only a starting point. A plant may approve a holiday shift, change a start time, or extend a production period.

Create explicit calendar instances for those exceptions. Record the approved version and effective period. Avoid a formula that assumes every weekday always has the same three shifts.

Suppose the day shift begins at 07:00 on one date instead of 06:00. The calendar owner must decide which interval owns events between those times. Extending the night shift and creating an unscheduled period are different choices.

Preserve the previous calendar version when making a correction. Keep the reason and the affected ShiftKeys. This lets the reporting team identify which event assignments and period totals may need to change.

Do not rebuild all historical shifts from today's weekly settings. That can silently change old reports after an otherwise routine schedule update.

Actual work may differ from the plan. State which calendar version the dashboard uses in that case.

Keep calendar date and production date available in Power BI

A shift table does not replace a proper date dimension. It adds a second level of meaning.

Use a date dimension for the agreed reporting calendar. Link the shift's ProductionDate to that calendar through a clear model design. Keep local event date available for investigations that require clock-day totals.

Microsoft's date-table guidance describes a consistent date dimension. It also explains what time-intelligence features require. A complete date calendar includes dates with no production. A list of production dates alone is a different thing. Power BI date-table guidance

Label the visible filter “Production date” when that is what it controls. A report page for raw transaction checks can use “Local event date.” Avoid changing the meaning of a shared “Today” button between pages.

At month end, a night shift starting on 31 October may continue into 1 November. Under the worked naming rule, its production date remains 31 October. Financial posting and shipment documents may follow other dates; do not force them to adopt the shift label.

Do not rely on display conversion

Power Query can represent a date, time, and timezone offset. Power BI's model handles loaded date/time values differently from that source representation.

Microsoft states that Date/Time/Timezone converts to Date/Time on loading into the model. The model does not automatically change that value to each viewer's local zone. Power BI data types

Prepare explicit display fields for the intended plant view. Retain UTC and the source zone in the data layer for investigation. Clearly label a timestamp shown to a remote buyer.

Test the same report in Desktop and after a service refresh. Compare a known overnight event rather than only a midday record. A six-hour error can remain hidden when both times still fall in the same shift.

Check the text format before another system uses the result. Has the format removed the offset? A readable timestamp is not necessarily an unambiguous exchange value.

Correct late and revised records through a clear rule

A late record can have a valid event time and a much later receipt time. Assign it using the approved event-time rule, then flag its late arrival separately.

Consider a 27 October night-shift record uploaded on 29 October. If its event time is verified, it still belongs to the original shift. The prior shift total may need restatement. The report should not quietly claim that the additional pieces were produced on the upload date.

A corrected event time can change shift membership. Preserve the original EventID. Keep the old and corrected timestamps, the approval, and the reason. Recompute the assignment and record which periods changed.

Do not use the report refresh time as a substitute event time when the source value is missing. Mark the missing value and resolve it through the data process.

The assignment logic and the historical refresh process must work together. A correct interval rule has little value if an old partition is never updated after a valid correction.

Check the device clock with evidence

Several events may sit exactly one hour or six hours away from the expected shift. That pattern can suggest a clock or conversion problem. It is a clue, not proof.

Compare the device record with a reliable event. One option is a controlled test scan observed at the station. Record the observer's time basis and the server receipt time.

Distinguish a constant offset error from a drifting clock or a delayed queue. They require different corrections. Preserve raw timestamps so a conversion can be fixed without inventing the original event.

Set an agreed review rule for implausible future times and very old uploads. Avoid a universal automatic correction such as subtracting one day from every outlier.

Where event-time accuracy is limited, state that limitation beside the shift comparison. Do not present minute-level precision as certainty when the capture process cannot support it.

Release a calendar with a reproducible test pack

Test normal start and end boundaries. Add midnight, month end, and a holiday. Include an extra shift and an unmatched event. Test an overlapping-calendar fault too.

For sites with changing offsets, include the relevant clock-transition cases. For every event, store the expected ShiftKey and assignment status.

Reconcile three counts: input events, uniquely matched events, and exceptions. Also compare source quantity with quantity attributed across shifts. Keep duplicate-event handling separate from duplicate-join handling.

Test an interval event across a boundary. Its allocated minutes must equal the original elapsed duration. Test an open interval using a fixed cutoff.

Ask a planner and a reporting owner to review the expected results together. They may discover that their use of “yesterday” differs. Resolve that definition before the dashboard becomes a performance measure.

Retain the test pack when a source system, timezone rule, or shift pattern changes. Rerun the affected cases after the change.

Include a written answer to one disputed event. A supervisor may call it part of yesterday's night shift while an analyst sees today's calendar date. Put the original timestamp, its time basis, the local interval, and the assigned ShiftKey on one line of the review sheet. Both people should be able to follow the rule without opening the report code. If they agree on the event but disagree on its label, resolve the display wording. If they disagree on the instant itself, return to the source record. Those are different defects, and changing a date label fixes only one of them.

Common questions

Can I assign shifts by subtracting six hours from the date?

That can reproduce one fixed pattern, but it does not describe exceptions, different plants, or changed schedules. An explicit interval calendar makes those cases visible.

Should a record at the end of a shift belong to both shifts?

No. Under the worked half-open rule, it belongs to the shift that starts at that instant. This prevents double assignment.

Can I use the upload date when the event time is missing?

Only if the business has explicitly chosen that different measurement. Label it as receipt or upload activity. Do not present it as verified production timing.

Must every event have a normal shift?

Every event needs a recorded assignment outcome. Some require an exception status until the calendar or timestamp is resolved.

Make the production date explainable

A useful shift calendar preserves the instant, the plant's local time, and the chosen production-date rule. Full interval boundaries and unique shift keys make the assignment traceable.

Take an event near midnight. Use the stored records to explain why it belongs to its reported shift. This is a strong practical check. The explanation should stay the same from source to refresh to dashboard. If it changes, fix the time model before comparing performance.

Shift boundaries affect production reports, so see production calendar calculations, factory KPI dashboard design, attendance data and skills coverage and line efficiency.

References

• Microsoft Learn — Local, fixed, and UTC time functions in Power Query.

• Microsoft Learn — DateTimeZone.SwitchZone.

• Microsoft Learn — AT TIME ZONE.

• Microsoft Learn — Set up shop calendars.

• Microsoft Learn — Date-table design guidance.

• Microsoft Learn — Power BI data types.

• ApparelDiary — WIP reconciliation and ageing reports.