Recursion problem

I am trying to implement the same logic , but I am not able to get the output as shown.
Can somebody help?Prov stock2_10.pbix (158.5 KB)

Hi @Anu,

The issue was a type mismatch.
Your file, had the Date formatted as Text


Once changed to a valid Date data type, the applied logic works as expected.


I hope this is helplful


From the above step,Do I need to expand the AdjProvStock Column? To get thisimage

Hi @Anu,

each of the nested tables contain the same data, so you might want to:

  1. extract the data from the same row from the nested table OR
  2. downdrill into one of the nested table objects

If you choose option 1
Add an Index column from 0 and add a custom column with this logic (shown inside the formula bar)

or for option 2
Right click next to any of the nested tables and select “Drill Down”

I hope this is helpful


If you don’t mind could you please share the file that you have done

Sure, no problem!

Both methods are implemented in this version of your file.

eDNA Prov stock2_10.pbix (162.3 KB)

One more question, if you would be so kind to check. I promise this will be the last on this thread

Suppose if I had two items in the source, Item A and B and the recursion logic has to be updated,
How can I edit the query to include it?
I mean the AdjProvStock has to be checked seperately for each item.

Hi @Anu,

Just add a currItem variable and refer to that in the Table.SelectRows filter condition, like below.
You can copy and paste this code into a new blank query

        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc6xDcAgDETRXVyDwAaUUMZrIPZfA1IQLpJpKJ7Q+bdGEnKQyJUccZzPQ929yrz4Qr2XesHfUpbXT9NeFsA97OFgwukMvJcL3Ps3q9msh2Y1m9Vq1kOz2s1qNk/tAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, StockMovement = _t, Item = _t]),
        SourceTyped = Table.TransformColumnTypes(Source,{{"Date", type date}, {"StockMovement", Int64.Type}}),
        CalcProvStock = Table.AddColumn(SourceTyped, "AdjProvStock", each Table.AddColumn(SourceTyped, "AdjProvStock", each 
        let currDate = [Date], currItem = [Item],
          Result = List.Accumulate(
            Table.SelectRows(SourceTyped, each ([Date]<= currDate and [Item] = currItem))[StockMovement]
            , 0
            , (state, current) => List.Max({state+current,0}) 
        in Result


I hope this is helpful.

1 Like