Supply Chain Management Techniques In Power BI

I recently ran a workshop on Supply Chain Management and I want to discuss the key insights from the session. This tutorial can be of great help if you have exposure to this kind of analysis in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

Supply chain management happens within a certain business operation that involves the flow of goods and services.

The sales cycle involves the order, shipping, and delivery of products.

When you perform this analysis in Power BI, you might find a lot of data. If you are just starting out in Power BI, things like this can get confusing, especially if you need to organize your model around multiple dates to extract multiple insights.

Table of Contents

Creating Inactive Relationships In The Model

The most important insight from a supply chain perspective includes the time between procuring the products to actually selling them. Thus, it’s important to know the average time between the Procure Date and Ship Date. Through this insight, you can tell the inventory available in the warehouse for a certain period.

Furthermore, it’s also essential to know the transition between the Order Date and the Delivery Date. This kind of data is useful from an inventory management perspective.

For this tutorial, I’m going to teach you how to get important supply chain management insights. It’s all just a matter of using the correct modeling techniques and correct Power BI formulas.

The first important thing that I want to recommend is to keep things simple by creating only one Dates table.

To get an insight between dates from other tables, you need to create an inactive relationship to your fact table. When you have a lot of key dates, you can create multiple inactive relationships. These inactive relationships should all go back to the Dates table.

Once you do it like that, you’ll realize how simple this stuff works. You can just default to creating inactive relationships like these in Power BI.

The next thing that I’ll teach you is the formula that you need to use to get an important inventory insight.

Working On The Events In Progress Technique

The next logical thing to do is to build up the core measures in the Key Measures folder. It includes Costs, Net Margins, Revenue, Transactions, and more.

The key to looking at insights over time is using a technique called Events in Progress. It’s a combination of DAX formulas that enables you to get the necessary insights. It is a very reusable technique.

Through this formula, you can see what is considered open inventory on any particular day. In the Inventory in Progress by Date and Warehouse Code table, you can look at the different warehouses data on a particular day. You can see if there are any orders with Order Date less than the Current Date and orders with Ship Date greater than the Current Date.

In the data highlighted above, you can see the inventory which has been ordered but still needs be shipped out. You can also view the corresponding warehouses where you can get and ship out the products to the right location.

It’s always important to optimize the inventory in the right locations geographically so you don’t have to ship to several locations. This is an important supply chain management technique that you should take note of and apply. This technique can also help you to determine if you have enough supplies for the needed deliveries.

The other formulas like Delivery in Progress or Order in Progress use the same formula pattern. You just need to use it in combination with the model. If you don’t have an inactive relationship in the model then the formula will not work.

Using The USERELATIONSHIP Function

After learning about inactive relationships of the data, the next question is how do you actually isolate this data? For instance, how do you look at the revenue by order date?

Since the context of date doesn’t automatically work in inactive relationships, you need to turn them on first. To do that, you need to use the USERELATIONSHIP function.

Here’s what it looks like:

With the USERELATIONSHIP function, you can turn on the inactive relationship between the Date column in the Dates table and the Date column in the Sales table. After that, you can take a look at the Revenue By Order Date by Date table.

Since the sample data is dynamic, you can just look at a particular warehouse and check how many inventories are open at any particular time. In the Inventory in Progress by Date and Warehouse Code table, you can also see how dynamic the numbers are depending on the season. The inventory data has its own ebb and flow and that’s where time intelligence can come in.

Conclusion

As I have mentioned, you can also branch out into time intelligence techniques from this insight, but that’s for another tutorial.

I just wanted to highlight this supply chain management technique that is perfect for supply chain types of analysis. You can also use this technique in many different scenarios where there are multiple dates. It’s not just limited to the scenario that I discussed.