PlantOps BI

Past-Due Backlog

Formula
Count (or $ value) of open orders past their promised date
Typical range
No external benchmark; the target is zero net growth month over month on a promise date that doesn't move
Direction
Lower is better

Open the Calculator Generate the DAX

Past-due backlog is the set of open orders whose promised ship or delivery date has already passed without the order shipping. It can be tracked as a count of orders, a dollar value, or both, and both views matter: a handful of high-value orders past due is a different conversation than a long tail of small ones.

What Good Looks Like

There’s no external number to benchmark against here, because backlog size depends entirely on order volume and lead time structure. The target that matters is trend: past-due backlog should not be growing month over month, and any order that goes past due should have a documented reason and a recovery date. A plant that’s honest about this number will see it move around with real capacity and material constraints; a plant that isn’t will see it stay suspiciously flat.

The flat-looking version is usually date creep. When a ship date is about to be missed, it’s easy to quietly move the promise date forward instead of reporting the order as past due. That keeps the backlog count clean while doing nothing for the customer waiting on the order. Lock promise dates once committed, and report date changes as their own metric so this doesn’t happen invisibly.

Past-Due Backlog in Power BI (DAX)

With an orders fact carrying promise date, ship date, and order status:

Past Due Order Count =
CALCULATE (
    COUNTROWS ( fact_orders ),
    fact_orders[order_status] = "Open",
    NOT ISBLANK ( fact_orders[promise_date] ) && fact_orders[promise_date] < TODAY ()
)

Past Due Value =
CALCULATE (
    SUM ( fact_orders[order_value] ),
    fact_orders[order_status] = "Open",
    NOT ISBLANK ( fact_orders[promise_date] ) && fact_orders[promise_date] < TODAY ()
)

Avg Days Past Due =
CALCULATE (
    AVERAGEX ( fact_orders, DATEDIFF ( fact_orders[promise_date], TODAY (), DAY ) ),
    fact_orders[order_status] = "Open",
    NOT ISBLANK ( fact_orders[promise_date] ) && fact_orders[promise_date] < TODAY ()
)

Add a promise_date_change_count column captured whenever a date is edited, and surface it next to backlog on the same page. It turns date creep from invisible into visible.

Common Mistakes

  1. Allowing promise date edits without an audit trail. Without one, this metric can be managed down without the underlying problem actually improving.
  2. Reporting order count without dollar value. Ten small past-due orders and ten large ones carry the same count but very different customer and revenue risk.
  3. Not separating root cause. A material shortage, a capacity constraint, and a quality hold all produce the same past-due order, but they need completely different fixes.

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

Past-Due Backlog 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

Page's typical range: No external benchmark; the target is zero net growth month over month on a promise date that doesn't move

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
Past Due Order Count =
CALCULATE (
    COUNTROWS ( fact_orders ),
    fact_orders[order_status] = "Open",
    NOT ISBLANK ( fact_orders[promise_date] ) && fact_orders[promise_date] < TODAY ()
)

Past Due Value =
CALCULATE (
    SUM ( fact_orders[order_value] ),
    fact_orders[order_status] = "Open",
    NOT ISBLANK ( fact_orders[promise_date] ) && fact_orders[promise_date] < TODAY ()
)

Avg Days Past Due =
CALCULATE (
    AVERAGEX ( fact_orders, DATEDIFF ( fact_orders[promise_date], TODAY (), DAY ) ),
    fact_orders[order_status] = "Open",
    NOT ISBLANK ( fact_orders[promise_date] ) && fact_orders[promise_date] < TODAY ()
)