Circular Reference in Open Orders
I am working on a stock rationalization logic where I have Orders as dimension and have to see how the SKUs will get distributed as per the requirements. Attached is a case representing the logic.
For the first 3 days, the supply is pooled and then allocation happens for the SKU. However, for Day 4, the closing inventory after day 3 has to be used, causing the logic to break due to circular references. Note that while I have done random allocations, the logic uses the inventory left after each allocation to decide what is to be allocated next.
Even if I use time as a dimension, I am not able to get the logic working straight because when I use Previous function, it looks at the previous date of the same order id. so for #87, it looks at 08/03/2021 of #87 whereas the actual data it needs to reference is in 08/03/2021 of #54.
I think i have spent too long getting confused so thought I would open it to the wider group for some ideas. Thanks!