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

How to monitor stock levels from forecast sales using DAX measures in Power BI

Part two 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
Stock depletion using forecast sales DAX (this blog)

Posted by Sam Lowrie on 05 November 2018

In this blog

The starting table
Breaking down the DAX
The result
Calculating the weeks remaining

Stock depletion using forecast sales DAX

The DAX for this solution can be tricky to follow, so I will show what is happening at each step. If you just want the DAX (and not the explanation), here it is:

Good luck! You could create all the variables as separate measures but this is neater (and more efficient!).

The starting table

This is the starting table. For each of these dates I want the point where my StoreStockLevel will run out:

For visualisation purposes I've chosen to follow what happens to the 11th September 2017.

How many weeks of stock do we have left based on a StoreStockLevel of 34 if we subtract the forecast sales starting on the 11th of September until we reach zero or less?

Breaking down the DAX

Let's start by capturing the current row's data:

Since variables are calculated once, this means for our example row we are following the value of getdate = 11/09/2017 and getstock = 34

Now we want to work out a running total of the forecast sales, but only for dates after the 11/09/2017. To do this we use the FILTER function which filters a given table:

This creates a virtual table stored in the variable which only contains dates after our current row. ALL removes the filter on the table caused by our measure.

So our virtual table would look like this for our specified date:

We've excluded all the dates which occurred prior to the 11/09/2017. Remember this is just a representation, in actual fact one of these tables has been generated for each of our dates!

Next we create a running total for the forecast sales so we can check when the predicted sales is greater than our current stock level. To do this we need to add up for each row the preceding sales again stored in a variable:

SUMMARIZE creates a table with a new column created by performing an expression on each row in the table.

We don't want to SUM all the rows since that isn't a running total. Instead for each row we want to sum the preceding dates and exclude the future dates. What effectively FILTER does is create this filtered table:

This is what the virtual table for the 09 October 2017 would look like. We create this table for each row in the SUMMARIZE table.

The SUMMARIZE then performs SUM on each of these tables to return the total forecasted sales up to that point in a column called Total. So for the 09 October 2017 it would be 42 which looks like this:

Remember this is a virtual table for the 11 September 2017 - one of these tables is being generated for every one of my dates!

What this allows me to do is check at what point the running total exceeded the StoreStock Level for 11/09/2017. Just eye balling it tells me the answer is the 09/10/2017 but let's use code:

Filter the tables to only include rows which have a greater running total than my original StoreStockLevel of 34.

My virtual table now looks like this. 09 October 2017 is the first date with a Total sales forecast greater than my original stock of 34:

Now I know the first week that my stock is insufficient to fulfil orders. I also know how many units I would need to for supply this and each subsequent week.

Since we are only interested in the first date its a simple matter of using the MINX function to retrieve it. MINX takes in a table and retrieves the smallest value from a given column:

Finally use the Return command to retrieve the minimum date and display it with pride in your visual!

The result

After all that hard work this is what the original table looks like now it includes our measure:

Some entries are blank since for these dates at no point does the running total of StoreStockLevel exceed the original stock level.

For each week we have the week where the stock would run out based on the predicted weekly sales. The next question is: how many weeks are there between the start and expiration dates?

Calculating the weeks remaining

Working out the number of weeks of stock left is relatively simple. We know the date of the last week with stock and the first week.

getdate holds the current row's commencing week, DATEDIFF works out the number of weeks between the current week and the date the stock runs out.

So for our first row getdate = 14 Aug 2017 which has a stock date expiry of 25 Sep 2017 , meaning that in 7 weeks the stock runs out.

Between the two dates there are actually 6 weeks of time (the weeks remaining). We need to add one week to return the number of weeks till the stock runs out.

Since these are measures, slicers will change the values in the table visual, giving the flexibility to remove unwanted data such as various products.

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Stock depletion using forecast sales DAX

The starting table

Breaking down the DAX

The result

Calculating the weeks remaining

Head office

London

Manchester