4,000 of 4,500 orders pass at final customer receipt. Orders with any accepted cancellation are left out.
KPI Definitions and Data Quality
Order-to-Delivery Metric Dictionary
Thirty versioned order-to-delivery metrics connect business definitions to named order and delivery fields, executed SQL, retained DAX measures and the reports that use them.
Implemented order-to-delivery definitionOpen image
Finance waits for the final customer receipt. The current report counts lines at warehouse despatch. The project shows the exact policy and implementation change needed to make the two reports agree.
One delivery rate, two answers
Finance reports 88.9%. The Power BI report shows 91.4%.
A wholesale distributor uses on-time delivery in its Finance pack, Operations review and Power BI report. Finance waits until the customer has received the complete order. The report counts each line when the warehouse despatches it.
The Director of Operations needs one decision: should a split order count as on time before its final quantity reaches the customer, and how should partial and fully cancelled orders be treated?
The definition before the formula
Measure one order at its final required customer receipt.
The agreed rule assigns the order to the month of its original promise. It subtracts only accepted cancelled quantity and passes the order when the remaining quantity is received on or before that promise.
- 01Start with one order
Use the stable order number and all lines that belong to it.
- 02Keep the original promise
The first customer promise assigns the order to a reporting month.
- 03Reduce only accepted cancellations
Partial cancellation changes the quantity still owed; full cancellation removes the order.
- 04Wait for the final receipt
A split delivery is complete only when the remaining quantity reaches the customer.
- 05Use one definition everywhere
The same numerator and denominator are retained for SQL, Excel and DAX.
Native metric catalogue
One large metric passport makes the disputed choices inspectable.
The workbook records 30 definitions across order intake, promise management, warehouse fulfilment, customer delivery, backlog, delivery service and warehouse capacity. Every definition has an executed SQL result, a retained DAX reference, a mapping to the order and delivery records, an owner, a version and an effective date.
The passport records the population, numerator, denominator, date, exclusions, owner, steward, target, implementation references, approval and limitation in one place.
Thirty definitions in one operating domain
The catalogue separates names that answer different questions.
Orders created and rolling 13-week intake are not interchangeable. Delivery cases raised and cases completed use different dates. Warehouse people and full-time-equivalent capacity remain separate measures. Each row states the rule and the reports it affects.
Six definition disputes recorded
A disagreement becomes a named policy choice, not another hidden formula.
The decision log records calendar month versus rolling period, created versus completed date, people versus FTE, reopened cases, gross versus net value and simple versus quantity-weighted average. Related measures keep different IDs and clear labels.
Order and delivery fields before the percentage
Every definition names the system, table, fields and calculation.
The catalogue uses the customer order system, warehouse despatch scans, customer receipt confirmations, delivery service cases and a monthly warehouse staffing file. The mapping shows where each measure begins before SQL or DAX applies the agreed rule.
Verified rule comparison
The percentage changes because the date, grain and population change.
9,140 of 10,000 lines pass at warehouse despatch. A split order can pass before the customer has received it in full.
4,224 of 4,800 orders pass. Accepted partial cancellations reduce the quantity still due; full cancellations are excluded.
One order through the rule
ORD-0004001 passes at despatch and fails at receipt.
The order has two lines and a promise date of 2025-06-10. Both lines leave the warehouse before that date, so the current report marks them on time.
Six units reach the customer first. The remaining four arrive on 2025-06-12, two days after the promise. The agreed order-level rule therefore fails the order.
Three retained implementations
SQL executes the rule; Excel recalculates it; DAX records the same intent.
DuckDB builds the order-level model and produces the verified numerator and denominator. The workbook independently applies the eligibility and receipt-date formula to 5,000 orders. The DAX and TMDL are checked for the same fields and operators but have not been run in Power BI Desktop.
Version and approval history
The agreed rule becomes effective only after the policy and tests are recorded.
OTIF moves from a documented disagreement to a tested definition and then to version 1.0, effective 1 July 2025. The full catalogue retains the same version, date, owner and approval state so a report author can see which rule should be used.
16 checks before publication
The result is rebuilt from the records, not typed into the page.
Seed 240605 recreates the same 120,000 orders, 250,000 lines and 180,000 events, 7,200 delivery cases and 96 warehouse-month records across 24 months.
- 01
All 30 metric IDs are unique and every required business rule, record mapping, implementation, version and approval field is present.
- 02
Every catalogue metric has one executed SQL result and a retained DAX reference.
- 03
The executed SQL produces 4,224 on-time orders from 4,800 eligible orders.
- 04
The Excel formula reaches the same 88.0% result across the 5,000-order comparison population.
- 05
Split, partial cancellation, full cancellation, missing promise and incomplete receipt cases are tested.
- 06
The DAX definitions are structurally checked but are not claimed as a native Power BI run.
Provenance and limits
A reproducible sample definition, not a claim about a live distributor.
The dataset is deterministic non-client example data generated from a documented order, line and delivery-event model. No customer or vendor records are used.
The project proves 30 executed SQL results, a headlessly generated Excel catalogue, retained DAX definitions and the order trace. It does not prove a deployed Power BI model, live approvals, production security or scheduled refresh.
A live organisation must approve its own promise date, completion event, cancellation policy, exclusions and target before adopting the definition.
Finance and operations reporting
On-time Delivery Metric Reconciliation
The SQL mart shows 38.81%, Power BI 38.38%, the Excel management pack 62.86%, the operational extract 38.83% and the manually maintained report 39.37%. The build explains the formula, grain, delivery event, filter or refresh time behind each value.
Decision: Which result should be used in the next review, and what must change in each reporting implementation?
- Platform
- DuckDB, SQL, Excel, retained DAX and report snapshots
- Data scale
- 120,000 orders, 250,000 lines and 180,000 delivery events, verified
- Maturity
- Native DuckDB, SQL and Excel reconciliation verified across five implementations; 19 of 19 checks pass; retained DAX not executed in Power BI Desktop.
Supplier reporting
Reporting Data Issue and Retest Register
A missing product code causes one supplier category to disappear from the monthly report. The product data owner corrects the reference table, a second analyst reruns the failed check and the reporting owner decides whether to release the report.
Decision: Has the missing mapping been corrected and independently retested well enough to release the supplier report?
- Platform
- Python, DuckDB, SQL and Excel, with Microsoft Lists and Power Automate deployment designs
- Data scale
- 120,000 orders, 250,000 lines and 180,000 delivery events; 60 incidents across 6 reporting cycles, verified
- Maturity
- Native Python, DuckDB, SQL and Excel build verified with 28 of 28 controls passing; no live Microsoft 365 tenant claimed.
Next step
Agree the delivery rule before changing the report.
Start with the measures people dispute or the failed checks that need clearer ownership before reporting.