560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
How to monitor stock levels from forecast sales using DAX measures in Power BI
Part one of a two-part series of blogs
Given your current stock levels and forecast sales, it should be possible to report on future stock depletion in Power BI. This two-part blog shows you how to just that!
For my fictional company I have the stock levels at the start of each week in addition to the expected sales for each week:
To keep things simple I will only consider a single store and a single product.
What I want to work out is this - based on each week's stock level, how many weeks will that stock last given the forecasted sales? Consider the very first row:
The starting stock level is 54 and that week's forecasted sales is 14, so the stock is sufficient for a least one week.
We know that when the running total of forecasted sales is greater than 54 that means my shop will be out of stock. So based on the stock available on the 14th of August when will my stock run out?
A quick measure shows us that on the 25th of September the predicted sales will have reached 57 (meaning that the 54 in stock will have been sold by then).
What I want to see is this expiry date for each of my weeks (and not just for the 14th August). Essentially it should look like this:
Each week commencing has a date on which the stock is forecast to run out (Worked out by tallying when the forecast sales outstrips the store stock level).
Once we have the starting week and the final week it will be possible to work out how many weeks the shop can operate in stock.
This means that our 14/08/2017 store stock level will be sufficient to last until the end of the week commencing 18/09/2017. This gives a total of 6 weeks of sufficient stock.
Again I don't want to work this out for just the first week. Instead I want to work it out for all weeks so that it looks like this:
All of this needs to be accomplished by measures so that it can be filtered using slicers!
Finally it would be great to have a slicer which could filter our table to only show stock levels that could last X number of weeks.
This helps us prioritise where the stock should be based on predicted sales.
That is the task; now we need to start writing the DAX!
Shout out to Helen Bailey who gave me this great question. It had me thinking for a few days and led to random late night emails!
|Parts of this blog|
25 Aytoun Street