How to Translate a Formula like "Cumulative Sum Until.. Sum reaches an Amount"?

2

Hi @Steelworld_HEAD,
it took me a while to figure it out…!

I found a solution, although it’s far from being perfect as it needs to overcome with the limitation of the lack of conditional loop (it’s still an open discussion).

I provided the implementation in your document, but basically these are the main componens:

  1. [Production Dates] Find the list of Production Items of that Item starting from the Start Date, and order them by date
[Table1, production].Filter(Item=thisRow.Item && [Date of Prod] >= thisRow.[Date Start])
   .Sort(true, [Table1, production].[Date of Prod])
  1. [Nth Position] Loop to iteratively have incremental sums (this is the tricky one…)
Sequence(1, thisRow.[Production Dates].Count()). // Walk through the Production Items
   FormulaMap( // for each index...
      If(thisRow.[Production Dates].Slice(1,CurrentValue).[QTY Produced].Sum() < thisRow.[QTY Ordered], // if the sum of all items up to the current index is less than the target quantity ...
          0, // print 0 
          1 // otherwise print 1
       )
   ).Find(1) // take the first occurrence of 1
  1. [Find Date Completed] Just select the date from that index
thisRow.[Production Dates].Nth(thisRow.[Nth Position]).[Date of Prod]

I’m sure there might be better approaches, but so far is the only one that comes to my mind

Obviously, if you have a huge amount of data, the computational burden might be relevant.

I hope this helps, anyway: let me know.

2 Likes