4,000 / 4,500
- Population
- 4,500 completed orders with no accepted cancellation
- Numerator
- 4,000 passing records
- Denominator
- 4,500 selected records
- Grain
- Order
- Event date
- Final customer receipt
- Owner
- Finance business partner
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.
Project previewOpen full 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?
Approved sample ruleUse one order at final customer receipt
Assign the order to its original promise month, reduce only accepted partial cancellations and exclude fully cancelled orders. Publish 88.0%: 4,224 of 4,800 eligible orders.
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.
MET-DEL-001On-time and in-full delivery
Give monthly service report one repeatable answer for on-time and in-full delivery.
- Metric ID
- MET-DEL-001
- Definition
- Share of eligible orders received in full by the original promise.
- Grain
- Order
- Numerator
- eligible orders completed by original promise
- Denominator
- eligible orders
- Date logic
- original_promised_date; final_receipt_date
- Owner
- Director of Operations
- Version
- v1.0 · effective 2025-07-01
Approved for this sample model
Used by Monthly service report; Finance management pack; Power BI delivery report. The sample limitation is retained with the definition, so the generated records are not presented as a distributor's live performance.
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 points to the system, table, fields and transformation.
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 / 10,000
- Population
- 10,000 order lines selected by the current report
- Numerator
- 9,140 passing records
- Denominator
- 10,000 selected records
- Grain
- Order line
- Event date
- Warehouse despatch
- Owner
- Reporting owner
4,224 / 4,800
- Population
- 4,800 eligible orders with promise and sufficient receipt data
- Numerator
- 4,224 passing records
- Denominator
- 4,800 selected records
- Grain
- Order
- Event date
- Final customer receipt
- Owner
- Director of Operations
SQLExecuted in DuckDB
build/sql/02_metric_model.sql#MET-DEL-001ExcelChecked in the workbook
Metric Catalogue!A17DAXRetained for Power BI
build/power-bi/order_delivery_measures.dax#MET-DEL-001One 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.
Original promise2025-06-1010 units required across two lines
Current line rule2 of 2 lines passBoth warehouse despatches occur by promise
Agreed order ruleOrder failsFinal customer receipt is 2025-06-12
- 01Line 1 · Despatch
6 units · PRD-0005
The current line-level report marks line 1 on time at warehouse despatch. - 02Line 1 · Customer receipt
6 units · PRD-0005
The first receipt supplies 6 units; the agreed rule waits for the remaining 4. - 03Line 2 · Despatch
4 units · PRD-0006
The current line-level report marks line 2 on time at warehouse despatch. - 04Line 2 · Customer receipt
4 units · PRD-0006
This final receipt completes the order two days after promise, so the agreed order-level rule fails.
Current Power BI
Passes both lines because each line was despatched by the promise date.
Finance
Fails because the final customer receipt was after the promise date.
Agreed
Fails at order grain because completion occurs at the final receipt.
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.
Approved ruleMET-DEL-001 · 88.0%
Share of eligible orders received in full by the original promise.
SQLExecuted in DuckDB
build/sql/02_metric_model.sql#MET-DEL-001
ExcelChecked in the workbook
Metric Catalogue!A17
DAXRetained for Power BI
build/power-bi/order_delivery_measures.dax#MET-DEL-001
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 report release
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.
- 01Orders contain the contracted row count
PASS · observed 120000
- 02Order lines contain the contracted row count
PASS · observed 250000
- 03Delivery events contain the contracted row count
PASS · observed 180000
- 04Order numbers are unique
PASS · observed 120000
- 05Every order line has an order
PASS · observed 0
- 06Every delivery event has an order line
PASS · observed 0
- 07Finance result is 4,000 of 4,500
PASS · observed 4000/4500
- 08Current Power BI result is 9,140 of 10,000 lines
PASS · observed 9140/10000
- 09Agreed OTIF result is 4,224 of 4,800 orders
PASS · observed 4224/4800
- 10Trace order has two lines and four delivery events
PASS · observed 2 lines / 4 events
- 11Trace order fails the agreed receipt rule
PASS · observed 0/1
- 12Order dates remain inside the 24-month contract
PASS · observed 2024-07-01 to 2026-06-30
- 13Every catalogue metric has an executed SQL result
PASS · observed 30 metric results
- 14Promise changes retain a valid order
PASS · observed 0 orphan promise changes
- 15Delivery cases retain a valid order
PASS · observed 0 orphan delivery cases
- 16Warehouse staffing covers 24 months and four warehouses
PASS · observed 24 months / 4 warehouses
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.
- The data is deterministic non-client example data and does not represent a real distributor.
- The DAX and TMDL are structurally checked but have not been executed in Power BI Desktop.
- The workbook does not prove a live approval workflow, production security or scheduled refresh.
- The agreed sample definition is not automatically right for another organisation, and the generated percentages are not service-performance claims.
Repeatability boundary
The verifier rebuilds the retained dataset from seed 240605, compares six output hashes, opens and resaves the XLSX in LibreOffice and rejects catalogue, trace, formula, image or reconciliation drift.
Finance and operations reporting
Why five reports show different on-time delivery rates
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.
What it helps answer: 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
- Project status
- The DuckDB, SQL and Excel results agree across five implementations, and all 19 checks pass. The matching DAX is included but has not been run 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.
What it helps answer: 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
- Project status
- The Python, DuckDB, SQL and Excel files pass all 28 controls. The Microsoft 365 workflow has not been deployed to a live tenant.
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.