OTIF (On Time in Full)
- Formula
- Orders delivered on time AND at full ordered quantity divided by orders due
- Typical range
- 90-95% is strong on most CPG scorecards; many retailers apply deductions below 95%
- Direction
- Higher is better
Open the Calculator Generate the DAX
OTIF measures the percentage of orders that arrive both on the promised date and at the full ordered quantity, with no partial credit for getting one right and not the other. It is a harder number than on-time delivery (OTD) alone, because a shipment that is on time but short, or full but late, still counts as a miss.
What Good Looks Like
Strong operations run 90 to 95% OTIF, and many retail and CPG customers formalize the target in a supplier scorecard with chargebacks attached below 95%. OTIF can’t be higher than the lower of its two parts. A plant with 97% of orders on time and 96% of orders in full lands somewhere between 93%, if no order misses both, and 96%, if every late order is also short.
The shortcut to avoid is multiplying on-time % by in-full %. Here that gives 93.1%, which is only right if late orders and short orders are unrelated. In practice they are often the same orders, and when misses overlap, the product understates real OTIF. Test both conditions on the same order and you get the real number, plus a list of the orders that missed. A fill rate measured in units is a different number again, so don’t use it as the in-full part.
OTIF in Power BI (DAX)
With an order fact carrying quantity and date fields, one row per order or one row per
order line, and dim_date related on promise_date:
Orders Due =
COUNTROWS (
FILTER (
VALUES ( fact_orders[order_id] ),
CALCULATE ( COUNTROWS ( FILTER ( fact_orders, NOT ISBLANK ( fact_orders[promise_date] ) && fact_orders[promise_date] < TODAY () ) ) ) > 0
)
)
OTIF Orders =
COUNTROWS (
FILTER (
VALUES ( fact_orders[order_id] ),
VAR DueLines =
CALCULATE ( COUNTROWS ( FILTER ( fact_orders, NOT ISBLANK ( fact_orders[promise_date] ) && fact_orders[promise_date] < TODAY () ) ) )
VAR GoodLines =
CALCULATE (
COUNTROWS (
FILTER (
fact_orders,
NOT ISBLANK ( fact_orders[promise_date] ) && fact_orders[promise_date] < TODAY ()
&& NOT ISBLANK ( fact_orders[actual_delivery_date] )
&& fact_orders[actual_delivery_date] <= fact_orders[promise_date]
&& fact_orders[shipped_qty] >= fact_orders[ordered_qty]
)
)
)
RETURN
DueLines > 0 && GoodLines = DueLines
)
)
OTIF % = DIVIDE ( [OTIF Orders], [Orders Due] )
OTIF % by Customer =
CALCULATE (
[OTIF %],
ALLEXCEPT ( fact_orders, fact_orders[customer_id] ),
VALUES ( dim_date[date] )
)
This is order grain. An order passes only if every line whose promise date has passed was delivered, arrived by its promise date and shipped in full, so one good line can’t pass the whole order. A line counts once its promise date is before today, so an open line past promise is a miss, and a line promised for today or later stays out.
OTIF % by Customer clears the other filters on the fact and on the tables related to it,
then puts the date selection back, so date slicers still apply. Take customer from
fact_orders[customer_id], not a customer dimension, because that clear also removes any
filter on the dimension.
Common Mistakes
- Multiplying on-time % by in-full % instead of testing both conditions per order. Late orders and short orders are often the same orders. When they overlap, the product comes out lower than true OTIF, and it can’t tell you which orders missed.
- Measuring at the line level instead of the order level. A customer doesn’t experience nine of ten lines shipped complete as a win; they experience one short item as a miss on the whole order.
- Leaving OTIF blended across all customers. A plant can average 94% while chronically shorting its largest account, and a blended number hides exactly the relationship most worth protecting.
OTIF Calculator
Runs in your browserResult
DAX Generator
Use My Own Table and Column NamesUpdates as You Type
Orders Due =
COUNTROWS (
FILTER (
VALUES ( fact_orders[order_id] ),
CALCULATE ( COUNTROWS ( FILTER ( fact_orders, NOT ISBLANK ( fact_orders[promise_date] ) && fact_orders[promise_date] < TODAY () ) ) ) > 0
)
)
OTIF Orders =
COUNTROWS (
FILTER (
VALUES ( fact_orders[order_id] ),
VAR DueLines =
CALCULATE ( COUNTROWS ( FILTER ( fact_orders, NOT ISBLANK ( fact_orders[promise_date] ) && fact_orders[promise_date] < TODAY () ) ) )
VAR GoodLines =
CALCULATE (
COUNTROWS (
FILTER (
fact_orders,
NOT ISBLANK ( fact_orders[promise_date] ) && fact_orders[promise_date] < TODAY ()
&& NOT ISBLANK ( fact_orders[actual_delivery_date] )
&& fact_orders[actual_delivery_date] <= fact_orders[promise_date]
&& fact_orders[shipped_qty] >= fact_orders[ordered_qty]
)
)
)
RETURN
DueLines > 0 && GoodLines = DueLines
)
)
OTIF % = DIVIDE ( [OTIF Orders], [Orders Due] )
OTIF % by Customer =
CALCULATE (
[OTIF %],
ALLEXCEPT ( fact_orders, fact_orders[customer_id] ),
VALUES ( dim_date[date] )
)