Lesson learned at the customer: Merging two tables to calculate On Time In Full (OTIF). [Part III]
In this final blog, we discuss the measures that have to be made.
Note: this is the third part of the blog. Missed the second part? Read it here.
From here on out we will continue with all the calculations that have to take place to get to the end result of the time aspect of OTIF.
Still in the transformation window and having our new merged table selected, we will start of with adding a new column to the table.
Under the tab “Add Columns” we select “Custom Column”.
The first custom column that we are making is one where we calculate a date difference between the Requested Delivery Date and Actual Delivery Date.
Repeat step 1. But now we are going to calculate an OnTime flag.
Lastly, we are going to calculate the measures for the time aspect.
I split up the measures since this allows for cleaner code in general. We will only need 2 measures to calculate our OTIF time aspect. Be aware that these measures are probably not the exact answer to your problem. These are context and data model dependent but the ones you see here can help you on your way.
And there you go! You have now successfully calculated your OTIF time aspect values!
Below you can find an image of how we situated our table in the model.
In order to apply filtering on our new table, we placed the merged table between our Orders and Deliveries tables in our model with a one to many, single directional relationship to Deliveries and a one to many bidirectional relationship to Orders.
The reason for the bidirectional relationship is so that we are able to filter the table with our calendar dimension. Right now filtering on the calendar table will filter the Orders table which in it’s turn will filter the SalesDocumentSalesItemKey column in our Merge table but this could very well be a different column to your liking.
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.