In this tutorial, you’re going to learn the complex measures and DAX formula used to create an inventory management dashboard in Power BI.
As a seller, you have to know how many products you need to re-order and stock and how much these will cost you.
Use these DAX formula patterns to get those calculations and results.
Checking The Inventory Level
Go to your Inventory Stock Control table and look at the Item Reorder Quantity column.
You can also see the Reorder Level column which is used as a scale for the quantities in your inventory. If the order quantity is less than the reorder level, you need to re-order.
You need to evaluate the total cost based on the item reorder level which is quantity multiplied by the cost per item.
For this calculation, create a new measure and name it Inventory Restocking Costs. Next, use the SUMX function to work the inventory stock table and the reorder level. Multiply that by the cost per item.
If you drag that measure into a card, you’ll see the result being 310K. However, it’s not evaluating if you are at the reorder level. All it’s doing is going through every row and cost per item multiplied by the item re-order quantity.
For the number to count up, you need to evaluate whether the stock quantity is less than or equal to the reorder level. To do that calculation, you need to add functions to the measure.
First, add CALCULATE at the beginning. Then after the SUMX function, add the FILTER function. Inside FILTER, put the inventory stock table. Next, evaluate if the stock quantity is less than or equal to the reorder level. If it does, return that number.
After that, you can see the change in the inventory restocking value which is 8,720.
To make it more logical, multiply the factors inside FILTER by 2. The new value in the card is now the expected inventory re-stocking cost.
Calculating Warehouse Stocks
If you want to know about the restocking by warehouse, copy the doughnut chart and change the Value to Inventory Restocking Costs and the Legend to WarehouseCode.
The next measure you need to create must calculate the actual quantities sold over a period of time. This helps you gauge if you have enough stock.
So, create a new measure and call it Quantity Sold Over Last 12 Months. Since this is historical data, you only need to work out the last day that you sold a product and from that day, go back to 12 months.
Next, use the VAR function. In the next line, name the variable LastSaleDate and input the LASTDATE function. Find OrderDate and then type RETURN.
After that, use an IF statement so that if the Inventory Stock Quantity is blank, the product won’t show up. Input CALCULATE then use SUM for the Order Quantity.
Add another function called DATESBETWEEN. For that function, use LastSaleDate – 365 as the start date and LastSaleDate as the end date.
If you drag that measure into the table, you’ll see the quantity sold over the last 12 months.
***** Related Links *****
Inventory Management Reports To Show Trends In Sales
Calculate Days Of Zero Stock – Power BI Inventory Management Insights
Inventory Management Insights With Power BI: Comparing Stock To Sales
Conclusion
The advanced DAX formula patterns discussed in this Power BI tutorial will greatly help you in getting valuable insights from your inventory data. Once you have all the calculations you need, the last step is to make your inventory management dashboard compelling.
I hope you apply this in your own Power BI reports. Check out the links below for more visualization and dashboarding techniques in Power BI.
All the best,
Sam
