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!

  1. Stock depletion using forecast sales - the problem (this blog)
  2. Stock depletion using forecast sales DAX

Posted by Sam Lowrie on 06 November 2018

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Stock depletion using forecast sales - the problem

For my fictional company I have the stock levels at the start of each week in addition to the expected sales for each week:

Stock depletion

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:

Forecast sales

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?

Stock

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:

Forecast sales

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.

Running total

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:

Weeks remaining

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.

Stock sales

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!

  1. Stock depletion using forecast sales - the problem (this blog)
  2. Stock depletion using forecast sales DAX
This blog has 0 threads Add post