A Power BI logistics dashboard earns its keep in the morning stand-up. Dispatch, warehouse, and finance should be able to point at the same shipment and agree whether it was on time, where it sat, and what it cost. If the page cannot answer those three questions without an Excel sidecar, the build is not done.
This is the how. The commercial case for hiring help lives on the logistics industry page. Here we cover KPI grain, page layout, and the mistakes that show up once TMS, WMS, telematics, and ERP share a model.
Start with grain, not with a map visual
Lock ShipmentId (or StopId if the customer books appointments per stop) before any card is drawn. TMS status events, WMS ship-confirm, telematics arrival, and ERP freight cost will not share that key on day one. Stage a shipment spine in SQL: one row per shipment, lifecycle timestamps (tendered, picked, departed, arrived, delivered), and a latest-valid-event rule so a corrected TMS ping does not double-count.
OTIF is a measure on that spine, not a column imported from the TMS “on-time flag.” Customer portals often use appointment windows; TMS may use geofence arrival. Write the tolerance (minutes early/late, by customer or lane) in the measure description. Until that definition is signed, do not average OTIF at carrier-week grain — you will hide the dispute instead of settling it.
KPIs that belong on the first page
- On-time in full (OTIF) at shipment or stop grain, with the appointment vs delivery-event rule documented.
- Freight spend and cost-to-serve: ERP accrual plus carrier invoice, shown with a reconciliation delta when they post on different days.
- Dwell and detention hours from WMS dock events and TMS stop times — the number ops actually argue about on the yard.
- Lane and carrier performance: volume, OTIF, cost per shipment, exception rate. Filterable, not a 40-row vanity table.
- Exception queue: late, damaged, missed appointment, shortage — with an owner field, not just a count.
Fleet cost per mile belongs on a second page unless finance already has clean telematics-to-asset mapping. Putting noisy GPS pings on the executive page is how the meeting goes back to Excel.
Visuals that survive a 15-minute ops call
Headline cards: yesterday OTIF, open exceptions, freight spend vs plan. One trend (14 or 28 days) for OTIF and cost — not both axes fighting. A matrix of lanes or carriers with conditional formatting on OTIF and cost, sorted by volume so small lanes do not dominate. Then a table of the exception queue with shipment, customer, age, owner. That table is the action list. Maps are optional; they look like logistics and often add nothing if the stand-up is about five late loads.
Use slicers for date, customer, DC, and carrier. Put Query reduction (Apply) on if the model is DirectQuery or Dual to a large TMS warehouse. Unbounded slicers at 07:00 will melt the source. Import on a 15–60 minute or nightly schedule is the default for board and ops packs; live GPS walls are a different product.
Common mistakes
- Importing the TMS on-time boolean instead of calculating OTIF from timestamps and a signed window.
- Expanding nested TMS JSON in Power Query after the fold boundary, then wondering why gateway refresh Status = Failed.
- DirectQuery to raw telematics pings. Aggregate to stop arrival/departure in SQL first.
- Showing cost as zero when finance has not posted. Use a “pending cost” state.
- Skipping RLS because “everyone in ops can see everything.” 3PL and multi-customer networks cannot.
Refresh, incremental, and the 05:00 pile-up
Large TMS history does not belong in a full reload. Once ShipmentDate (or a trustworthy ModifiedDate) folds to SQL, incremental refresh with RangeStart/RangeEnd is the honest pattern on Premium, Fabric, or PPU. Without a watermark, corrected events that restated last month never re-enter the partition. That is how OTIF “mysteriously” diverges from the TMS screen two weeks later.
Stagger DC extracts. Five warehouses hitting the same SQL instance at 05:00 looks like a capacity problem and is usually a schedule problem. Document outbound 443 and SQL ports from each subnet; GatewayNotReachable after a firewall change is more common than a bad PBIX.
UAT that matches the stand-up
Pick three shipments: one on-time, one late with a correction, one with pending cost. Reconcile OTIF and cost to TMS timestamps and ERP invoices for those three — not to a weekly average. If View Native Query is gray on the staging query, stop UAT and fix folding. A pretty page on an unfolded mashup will fail on the gateway even if Desktop looks fine.
Have the dispatcher run the exception queue in the real meeting for two cycles before you call the release done. If they still paste into Excel to sort by customer, the table visual is wrong — too many columns, no owner, or the grain is stop when they think in shipment.
If shippers share a tenant, row-level security on ClientId is not a phase-two ticket. Test View as for a shipper user and an internal control-tower role before go-live. Schedule refresh on an On-premises Data Gateway (standard) cluster — not a dispatcher laptop. Stagger DC extracts so five warehouses do not hit SQL at 05:00.
When you want the commercial framing and TMS/WMS connector list, use Power BI for logistics. For a shipment-spine example, see the service and cost-to-serve case study.