The report was designed to showcase the On Time In Full (OTIF) status of all orders for the past two years.
Each shipment is assessed on two criteria:
1) If the full quantity was shipped
2) If the order was shipped on the Due Date
The report has an Allowable Window, where a Customer might be able to accept shipments upto sixty day early or late.
Once a user has selected the Allowable Window, the
rest of the report will use this window to calculate the OTIF status.
The Trend Buttons allow users to investigate through the report without having to leave the report page and keeping any applied filters.
The Columns, Parameters and DAX:
There is one additional column, two parameters and a host of measures.
Days - the only column
The data has columns Due Date and Ship Date, therefore we can use DATEDIFF to calculate the difference in the two dates. I use a column for this.
Days = DATEDIFF( OTIF[Ship Date], OTIF[Due Date], DAY )
Early and Late Windows - the two parameters
The Early and Late Parameters are set up as Whole Number, and they automatically generate measures: Early Value and Late Value. We use these as the allowable window.
The Measures
In Full
If an order has not been fully delivered then the order must be late, so we can work out the completeness of the order using a measure to compare the Order Qty and the Delivered Qty:In Full =
IF(
SUM(OTIF[Order Qty]) = SUM(OTIF[Delivered Qty]),
"In Full", "Partial"
)
With these three pieces of information we can work out if an order was delivered In Full and On Time, including any user entered Allowable Window:
On Time Shipment
To count as an on time shipment, the order must be In Full and the days difference must be less than the allowable early and the allowable late. We will count the number of rows on the OTIF table to fit these criteria.
On-Time Shipments =
CALCULATE(
COUNTROWS( OTIF ),
FILTER( OTIF,
Early[Early Value] >= OTIF[Days] &&
(-Late[Late Value]) <= OTIF[Days] &&
[In Full] = "In Full"
)
)
Early Shipments
These are one criteria easier than On Time, if the Days is greater than the allowable early and it's In Full, then the shipment was early:
Early Shipments =
CALCULATE(
COUNTROWS( OTIF ),
FILTER( OTIF,
OTIF[Days] > Early[Early Value] &&
[In Full] = "In Full"
)
)
Late Shipments
The late shipment involves an extra criteria to check tha tthe order qty has been fully shipped. If the order was shipped after the due date OR the order is partial, then it will be counted as Late:
Late Shipments =
CALCULATE(
COUNTROWS( OTIF ),
FILTER( OTIF,
OTIF[Days] < -Late[Late Value] ||
OTIF[In Full] = "Partial"
)
)
The OTIF graph is made up using the Date heirarchy from the Date table and then adding the three measures as values
Status
Now that the data has been assessed, we can report back the Status of each individual row, with another measure:
Status =
IF(
COUNTROWS( OTIF ) >0,
IF( [Early Shipments] >0, "Early",
IF( [Late Shipments] >0, "Late",
"On-Time")
)
)
