Automating shift-wise utilization reports for JCBs and earth movers with Power BI and Power Automate

In plain words: Power BI is the calculator and the dashboard. It takes the raw sensor records, works out which shift every minute belongs to, and calculates engine, idle and productive hours that everyone can trust.

This is the heart of the solution. The raw data is a list of time intervals per machine. The business wants hours per machine, per day, per shift. Getting from one to the other meant solving three problems.

Problem 1: activity that crosses a shift change

The plant runs Shift A from 06:00 to 14:00, Shift B from 14:00 to 22:00 and Shift C from 22:00 to 06:00. A machine that starts work at 13:20 and stops at 15:05 worked in two shifts, and each shift should get its share.

In Power Query, we generate split points at 06:00, 14:00 and 22:00 for every day a record spans, cut the record into segments at those points, and share the reported duration across the segments in proportion to their length.

Problem 2: a night shift that belongs to yesterday

Shift C starts at 22:00 and ends at 06:00 the next morning. Activity at 01:30 on 19 February belongs to the night shift of 18 February. A calculated Shifted Date moves any segment that starts before 06:00 back one day, so each night shift is reported as a single unit on the day it began.

Power Query (M), simplified

// 1. Cut each record at 06:00, 14:00 and 22:00
shiftTimes      = {#time(6,0,0), #time(14,0,0), #time(22,0,0)},
validBoundaries = List.Select(boundaries, each _ > start and _ < end),
splitPoints     = List.Sort(List.Combine({{start}, validBoundaries, {end}})),

// 2. Share the reported duration across the segments
NewSec = [OriginalSec] * ([SegmentSec] / [TotalSec]),

// 3. Night shift belongs to the day it started
#"Shifted Date" = if Time.Hour([SegStart]) < 6
                  then Date.AddDays(Date.From([SegStart]), -1)
                  else Date.From([SegStart]),
Shift = let h = Time.Hour([SegStart]) in
        if h >= 6 and h < 14 then "Shift A"
        else if h >= 14 and h < 22 then "Shift B"
        else "Shift C"

Problem 3: two data sources, one row per shift

Engine time and idle time arrive in separate tables, and a given shift can appear in one but not the other. We gave both tables the same composite key, Machine & Shifted Date & Shift, and built a bridge table from every distinct key in both sources. The bridge is split back into Machine, Date and Shift columns and linked to a vehicle master, an Excel list in SharePoint that supplies the vendor or yard, registration or invoice number and PO number.

The result: every machine-shift combination appears exactly once, and engine and idle figures always line up in the same row, even on days when one source has no records.

Measures that stay correct past 24 hours

Durations are summed in seconds and formatted as hh:mm text. Power BI's time type can't show more than 23:59, so a machine that logged 30 hours 15 minutes across a date range would display wrongly. Formatting the total ourselves keeps it right. Productive hours are engine seconds minus idle seconds:

DAX

Productive Hrs =
VAR EngineSec = SUM ( 'Engine Running Time'[duration sec] )
VAR IdleSec   = SUM ( 'Compound Report'[duration sec] )
VAR s = EngineSec - IdleSec
VAR H = INT ( s / 3600 )
VAR M = INT ( MOD ( s, 3600 ) / 60 )
RETURN
    IF ( H = 0 && M = 0, BLANK (), FORMAT ( H, "00" ) & ":" & FORMAT ( M, "00" ) )

One table, on purpose

The report has a single main visual: a matrix. We considered charts and deliberately left them out. The people reading this report check hours against hire bills and shift logs. They need exact figures for every machine, day and shift, not trend lines.

In plain words: Report Builder designs the printed page, and Power Automate is the postman. Every day it prints one PDF per vendor or yard and emails it to the right person.

Power Automate on its own can email Power BI data as an HTML table in the body of a message. That wasn't good enough here. Vendors needed a proper PDF that looks the same on every screen and printer, with a clean header, a fixed layout and tidy page breaks. That's exactly what paginated reports are designed for, so we added one to the workflow.

1. Publish the semantic modelThe Power BI model is published to the Power BI service, where it refreshes on a schedule from SharePoint.
2. Build the paginated report on the same modelIn Power BI Report Builder we connected to the published semantic model and rebuilt the table for print. The dashboard and the PDF use the same DAX measures, so there's one source of truth and no duplicated logic.
3. Add report parametersWe defined parameters for vendor or yard and for date, wired into the dataset query. They're what let an outside process ask for one recipient's report for one day.
4. Publish to a Premium workspaceThe paginated report is published to a workspace on Premium capacity, which the export step requires.
5. Distribute with Power AutomateA scheduled flow loops through the vendors and yards. For each one, the Export To File for Paginated Reports action renders a PDF using that recipient's parameter values, and an email action sends the file as an attachment.