topic Re: OTIF Report in Data Stories Gallery

OTIF Report

Andy100Rob — Fri, 11 Aug 2023 15:53:46 GMT

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"

)

OTIF Graph
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")

)

eyJrIjoiNmIwYjAxZmEtMzE4OC00MDExLTlhYjAtM2FiMmMyMWQxNzUxIiwidCI6ImM3ZWE5NDI0LWNkZWMtNGE5Zi1iYzljLWE3MDkwOTMwNjk0NCJ9

Re: OTIF Report

Anonymous — Fri, 04 Sep 2020 07:54:14 GMT

Hi,

My company is interested in getting your report - could you tell me hwo it would be possible ? What is the price for it including possibility of further edition ?

Regards

Witold Adamiak

Re: OTIF Report

Anonymous — Thu, 18 Mar 2021 08:29:58 GMT

Hi Andy100Rob,

Am i able to get a complete copy of this report, or other examples, is it free or can it be purchased. I have been asked to do a similar report for my company, however i am a very new Power Bi User, so i need some guidance, i was more of an Excel user in the past, however very excited to start using Power BI.

Re: OTIF Report

Andy100Rob — Sun, 18 Apr 2021 18:36:00 GMT

I've put the DAX up, if you still wanted the report

Re: OTIF Report

Andy100Rob — Sun, 18 Apr 2021 18:36:26 GMT

I've put the DAX up, if you still wanted it.

Re: OTIF Report

Anonymous — Sun, 18 Apr 2021 21:07:01 GMT

Sorry, they have still not made a decision

Re: OTIF Report

Anonymous — Mon, 19 Apr 2021 06:06:44 GMT

I'm stil interested in getting the report...

Re: OTIF Report

cneri17 — Fri, 14 May 2021 17:01:33 GMT

Great report. I was having a hard time trying to desing an OTIF report for my organization until I came accross this post. Thanks for sharing!

Re: OTIF Report

Anonymous — Mon, 06 Dec 2021 21:38:08 GMT

Hello,

Sorry, I'm new here.

I love this and it's perfect for what I'd like to use, is it possible to download it with the data so I can use it, or is this protected?

Sorry for the dim question, I'm new to Power BI.

Thanks again

Re: OTIF Report

Nikita_1990 — Mon, 17 Oct 2022 09:17:42 GMT

This is indeed very helpful. I could complete my OTD report in couple of hours with above mentioned reference Dax. Interested in seeing more KPIs like these related to supply chain..

Re: OTIF Report

Ugochukwu — Sat, 19 Nov 2022 14:29:34 GMT

Can you please share dataset for the dashboard, I will like to use it for some other portfolio project

Thanks

Re: OTIF Report

Anonymous — Wed, 30 Nov 2022 21:31:12 GMT

Hi Andy100Rob, thanks for sharing and I've spent a lot of time trying to achieve a new OTIF report at work that unfortunately, does not look anywhere near as nice as the one you have created.

Can I be cheeky and ask if you could share the file with me so I can try and use it for my data?

Thanks a million

Re: OTIF Report

jurkowskij — Tue, 25 Apr 2023 14:41:38 GMT

Hi,

Great report. Gave me some ideas to implement in my organization

Question: How to create this "mini windowed" visualizations like below?

It's some kind of use of drill trought?

Re: OTIF Report

Andy100Rob — Tue, 25 Apr 2023 14:57:15 GMT

That's a Tooltip page.

The page only has that graph on it. You just need to add a new page, set it as a Tool tip and then from the original graph, you can choose to use the specific tool tip page.

Re: OTIF Report

Anonymous — Thu, 27 Jul 2023 07:39:07 GMT

Can I have the report please ? I have a project to show OTIF Performance and I need it !

Thank you !

Re: OTIF Report

Power_rookie — Tue, 22 Aug 2023 12:41:58 GMT

hello, is it possible if i could have a copy of the report please so i can understand it better.

Re: OTIF Report

KK303 — Wed, 30 Aug 2023 18:29:56 GMT

Can I get a copy of this template?

Re: OTIF Report

rinaredelinghuy — Fri, 22 Sep 2023 12:50:50 GMT

Hi, please share the report with me?

Re: OTIF Report

NBW — Wed, 11 Oct 2023 09:58:50 GMT

Hi,

I would like to know the DAX for STATUS part.

how do you use allowable window value.

Re: OTIF Report

OTIF — Tue, 09 Jan 2024 10:32:05 GMT

Great Work!! Is there any possibility to get the template?

Regards

OTIF