The Hidden Treasure of Supply Chain Management: Uncovering Valuable Insights for Business Success
Have you ever wondered how businesses manage to produce and deliver goods and services to you in a timely and cost-effective manner? Well, the secret lies in supply chain management. It involves overseeing the entire process from raw materials to final delivery, and it plays a crucial role in the success of any business that deals with physical products.
By analyzing various aspects of the supply chain, businesses can gain valuable insights that help them make informed decisions and optimize their operations. For instance, they can monitor inventory levels to identify slow-moving or excess stock and take corrective actions to reduce waste and improve cash flow. This can help businesses optimize their inventory levels and reduce the cost of carrying inventory.
Transportation management is another critical area where insights can be gained from the supply chain. By analyzing transportation data, businesses can identify inefficiencies in the transportation process and take corrective actions to reduce transportation costs and improve delivery times. This can help companies to optimize their transportation operations and enhance the efficiency of their supply chain.
Furthermore, supply chain analytics can help businesses predict future customer demand by analyzing historical data. They can adjust their production and inventory levels accordingly, reducing the risk of stockouts and improving their overall customer service levels.
Today we will be focusing on transportation management
The order process is one area where insights can be obtained from the supply chain. By tracking orders from initiation to delivery, businesses can gain insights into their supply chain performance and identify areas for improvement. For instance, by monitoring orders in progress, companies can identify delays in production and take corrective actions to prevent further delays. This can help to reduce lead times and improve customer satisfaction.
Another area where insights can be gained from the supply chain is orders awaiting shipment. By monitoring the status of orders ready for shipment, businesses can identify bottlenecks in the shipping process and take corrective actions to speed up the delivery process. This can reduce order cycle times and improve the efficiency of the supply chain.
Finally, delivering orders is another area where valuable insights can be obtained from the supply chain. By tracking the delivery of orders, businesses can identify areas where delivery times can be improved and ensure that products are delivered on time which can help to improve customer satisfaction and increase repeat business.
Now that we have seen the business use case let’s go into the technicality. How do we use PowerBI for transport management?
Let’s go into PowerBI, where the magic lies
First, supply chain data will have multiple dates on the fact table. When working with multiple dates in your fact table, it is advisable to always set the relationship as inactive and turn on those relationships in your DAX measures using functions such as USERELATIONSHIP, FILTER etc. Here’s what our model looks like.
You can download the data here. In my model, I have created an inactive relationship between the calendar table and the four dates on the Sales table, i.e. procured date, order date, shipment date and delivery date.
Remember that our metrics include the following:
- Orders in progress, i.e. orders that have been placed but not yet delivered
- Orders awaiting shipment, i.e. orders that have been placed but not shipped yet for delivery
- Orders undergoing delivery, i.e. orders that have been dispatched for delivery but have not yet been delivered
Before we can write our DAX measures that calculate those metrics, we need to have our base measures
- Product Qty
2. Revenue
3. Number of orders
Let’s start with writing the DAX measures for the metrics
Orders in Progress
Before we go into this, let’s think of the logic together. What are the conditions that should be met for an order to be considered as in progress? You ordered a product on a particular date, and for that transaction to be complete, the product needs to be delivered on a specific date. If there is no delivery date, the transaction is incomplete, and the order is still in progress. The DAX measures required need to follow similar logic.
The DAX above counts the number of orders that were made but are yet to be delivered on that particular date. Note that the calendar date will provide context.
Let’s go behind the scenes to see how it works
The date column on the Calendar table provides context to the DAX measure. The first variable, order_date_filter, means that we filter the sales table only to return orders whose order date is less than or equal to the maximum date. As seen in the image below, the max and min dates are identical for a date context. For example, on 14/1/2018, the order_date_filter will return a list of orders raised on or before that date (14/1/2018).
The delivery_date_filter will return a list of orders whose delivery date is greater than or equal to the min date. The CALCULATE function then counts the number of orders that satisfy the filter conditions.
The DAX measure works in multiple ways based on the context applied. The numerous ways result from the change in Min and Max Dates. So, let’s go through the three different contexts.
- Date context
- Month context
- Year context
Date Context
The min and max dates are the same for every row. Consider the table below.
On 12/6/2018, there were 100 ongoing orders. The DAX counts the number of orders whose order date is less than or equal to 12/6/2018 and the delivery date is after or equal to 12/6/2018.
Month Context
For a month context, the max and min dates are different. The min date is the 1st day of the month, and the max date is the last day of the month. Using Aug 2018 as an example, the measure returns the number of orders whose order date is on or before 31/08/2018 and whose delivery date is after or equal to 1/8/2018
Year Context
This works similarly to the Month Context. Here, the max and min date is the last and first day of the year.
Orders Awaiting Shipment
For this, the logic is orders that have been placed but haven’t been shipped.
The DAX measure works the same way as the order in progress based on the date context applied.
Orders Being Delivered
The DAX for this is as seen below
Now that we have all our DAX measures available, we must create our report.
In conclusion, the supply chain provides a wealth of insights businesses can use to optimize their operations and improve their bottom line. By monitoring the order process, orders awaiting shipment, and orders being delivered, companies can better understand their supply chain performance and identify areas for improvement. This can help to reduce costs, improve efficiency, and enhance the overall customer experience.
Looking for more in-depth analysis and data-driven insights? I’m thrilled to be contributing to the fantastic TA Insight HUB blog! (www.tainsighthub.com) or on Twitter here and linkedIn here
Catch up on everything data from me and other experts over there. Let’s keep the data conversation going!
