This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Stock depletion using forecast sales - the problem (this blog) Stock depletion using forecast sales DAX

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!

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

Posted by Sam Lowrie on 05 November 2018

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:

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: