Recently Rúben talked to me about a difficulty he was having: He needed to build a Provisional Stock calculation in Power BI based on Sales forecast and Order arrivals in a way that seemed to require recursivity, and he couldn't find a way to make that happen in Power BI using DAX.

DAX currently lacks support for recursion, but given the fact that the Power BI model in question was built over file only, we didn't want to bring a SQL infrastructure just to solve this problem. So we tried a different approach, but let's start from the beginning.

The Problem

Consider the following data table.
Table 01: Data source

We have the predicted stock exits (sales forecast) and the predicted stock entrances (orders arrivals) listed by date. In the column on the right, we’ve calculated the total stock movement (Entrances - Exits).

We want to calculate the provisional stock at each given date as per the movements (let’s assume the stock was zero previous to the first date).

It doesn't seem overly complicated, right?
Table 02: Accumulated Movements

Well, not exactly! As much as our predicted sales happen to be accurate, we will definitely not sell more than the stock we might have. So, those negative stocks don’t really make sense. Let’s adjust it a little then, and it should look like this:

Table 03: The goal

OK, notice what we did here:

  • On Feb 18th, where previously we had a provisional stock of -3, we assumed the minimum of zero (there are no negative stocks!);
  • Then, we considered zero to add to the next week movements, resulting in a provision of 9 instead of 6;

As you see, this will affect the remaining rows resulting in totally diferent values.

Despite this being quite simple to achieve in Excel, it isn't as simple to do in Power BI, and this was the problem we had to solve.

The solution

As I explained to you in the beginning, we didn’t want to bring SQL to the table. So now I’m going to show you how we solved this using Power Query.

Firstly, we loaded our power query table with the dates and the stock movement.

Table 04: Power Query source

Then we used the very handy function List.Accumulate. Never heard of it? Neither had we, but it was a life saver.

List.Accumulate(list as list, seed as any, accumulator as function) as any

So, the function will receive a list, a first state and a function with the agregation logic.

= Table.AddColumn(SourceTyped, "AdjProvStock", each 
    let currDate = [Date],
      Result = List.Accumulate(
        Table.SelectRows(SourceTyped, each ([Date]<= currDate))[StockMovement]
        , 0
        , (state, current) => List.Max({state+current,0}) 
      )
    in Result
)

We passed it our StockMovement column, for all rows from previous dates (relative to the row we are calculating).

Table.SelectRows(SourceTyped, each ([Date]<= currDate))[StockMovement]

For the initial stock state, we assumed zero.

And for the aggregation function, we just take the previous state and add it to the current one, never allowing the result to be less than zero, and for that we've used the List.Max function:

(state, current) => List.Max({state+current,0})

Table 05: the final result

Pretty simple, right? Well, it didn't seem to us for all the hours we've spent looking for a way to achieve it. I hope this post can help you avoid the same trouble.

Take care!