PlantOps BI

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

  1. 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.
  2. 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.
  3. 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.

Table and column names in the DAX above are examples. Use the DAX generator below to fill in your own.

OTIF Calculator

Runs in your browser

Nothing you type is sent anywhere. Your inputs only go into a URL if you click Copy Share Link, and anyone with that link can read them.

Result

DAX Generator

Use My Own Table and Column NamesUpdates as You Type

Type your own table and column names. The measures update as you type. Leave a field blank to keep the example name.

Tables
Columns
Your measures
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] )
)