Lesson learned at the customer: Merging two tables to calculate On Time In Full (OTIF).
It’s always fun to make your customers happy and learn new stuff by simply just doing your job. That’s what we did at one of my customers lately. Us and a colleague (at the customer) figured out a way in Power BI to calculate the OTIF over multiple fact tables. For both beginner and intermediate profiles we thought this would be interesting to share.
And so we did! In this blogpost we will explain the process we went through to make the OTIF calculation happen.
What is OTIF?
To start with let us first explain what OTIF is for those who don’t understand it yet. “OTIF” stands for “On Time In Full”. This is a measure to see how many of your orders are delivered at the correct requested delivery date and how many of your orders are delivered in correct quantities.
This means that there are two aspects, both represented as a percentage of the total SalesDocumentSalesItemKeys (in our situation), that have to be measured before being able to follow up on an OTIF analysis:
- Time aspect
- Quantity aspect
The quantity aspect is simple. You compare the ordered quantity to the delivered quantity and if there is no difference, you count this as an “In Full delivery”.
The Time aspect of the whole ordeal is less simple. We have to figure out a way to compare the Requested Delivery Date, situated in the Orders Table, to the Actual Delivery Date, which is the normal date column in our Deliveries Table.
The problem statement
Initially one would just make a measure to calculate the difference between the Requested Delivery Date from Orders and the Actual Delivery Date from Deliveries on the spot. Though this was an option, it wouldn’t have been the best one due to the mere size of the dataset and the fact that we are calculating this over multiple fact tables. That’s why we thought it was best to move this calculation to the transformation part of Power BI and use Power Query to go about calculating the Time aspect for OTIF.
The first thing we did was go to the transformation part of Power BI and reference (not duplicate) the Orders and Deliveries tables In order to make sure that any changes to the tables only have to be made in the original and not twice, once in the original and once in the copy.
These tables are copied because we will eventually bring these tables back to the columns that we will eventually need to bring together in one table. These columns are the following:
- From Orders:
- Requested Delivery Date
- From Deliveries
- Calendar Day
- This is the normal date column that will become the Actual Delivery Date later on.
The first table you start with, is the Orders table since this is the first table where something happens first in a logistics related process. This is also the table where, we would assume, you can find the most SalesDocumentSalesItemKeys.
Where to Perform Transformations?
Within this blog, we will use PowerQuery for data-transformations. However, we could perfectly have done it using DAX expressions. Why do we prefer PowerQuery over DAX? As a general rule of thumb, we want to perform transformations as close to the source as possible. Hence, if the source system would’ve allowed data transformations (such as SQL) then we would’ve done that instead.
How to navigate to the Transform Data part of Power BI
We started out by exploring our dataset and getting to know it. First we checked if there are duplicates in the SalesDocumentSalesItemKey column by using the Group By functionality in Power Query. This is also a good way to figure out whether your table is a historical table or if it already contains the most up-to-date record for every key.
After having figured out if there are any duplicates for your key column, don’t forget to delete this step. This is merely exploratory.
Figure out which column causes duplication by filtering on one key (with duplicates of course) and searching for columns that show any differences in record values.
For us this was a record where stages of the process were being saved (i.e. for an order: processing – fulfillment – fulfilled).
Next you should go ahead and find the column that allows you to filter out the most up-to-date record for all of the instances of your key. In our example, the most recent record was the one with the latest “Created on” date.
Let’s call this kind of column the “MRC” (Most Recent Column) for future reference.
Group By functionality was used to filter out the records with the most recent instance of the key based on the MRC. To do this you apply the Max operation on the MRC. Make sure to use the advanced section of the Group By to add the rest of the columns as well.
Your result will look something like the following:
For the column that’s generated which contains the rest of the table, click the arrows on the right-hand side of the column header. This opens the expand menu where you can select the columns you still need to include in the table you are creating.
Here you deselect all columns and search and select the column(s) you need for the calculation of the time aspect of your OTIF.
Now is also a good moment to rename the column(s) from its generated name to something more simple and meaningful.
Now you can go ahead and delete the MRC column (here “Latest Record”) since you have no use for it anymore.
Remove duplicates based on your key column (SalesDocumenSalesItemKey).
Repeat step 1-3 for orders but instead of searching for the Requested Delivery Date in step 3, you search for the columns that contain the Actual Delivery Date.
We will keep referring to this column as “Actual Delivery Date”.
Don’t forget to rename the columns like you did in step 3.
Correspondingly, you can now go ahead and remove the MRC column that you created with your Group By.
You guessed it! It’s time to remove the duplicates again based on the SalesDocumentSalesItemKey.
This blog will continue in part 2, where we bring everything together in one table.
Tibo is a BI consultant with a mere focus on Power BI and data modelling. His interests mainly lie in DAX writing, development of Paginated Reports and Power Apps.