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!

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

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 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:

DAX

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:

Unfiltered table

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:

Variables

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:

Measures variables Dax

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:

Virtual table

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

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:

FILTER

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:

DAX

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:

DAX

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:

DAX

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:

DAX

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:

DAX

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.

Weeks remaining

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.

Dates

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.

 

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