Forum Discussion
SimoneBraccio
Dec 17, 2024Copper Contributor
Fifo days in inventory logic
Hi,
I have been trying to build a dynamic array logic to dynamically automate the calculation of days in inventory of some products. I am not an expert in using functions like scan/lambda and I have been running into issues related to the fact that a selling transaction might involve selling items from different purchaes batches, with different transaction dates.
Could someone advice on an automated way to to this? (the image below is just an example, but as you can imagine the dataset is much bigger and of changing dimensions based on the day).
You may start from this:
=LET( purchaseDates, A2:A10, // Range of purchase dates quantitiesPurchased, B2:B10, // Range of quantities purchased saleDates, C2:C10, // Range of sale dates quantitiesSold, D2:D10, // Range of quantities sold runningTotal, SCAN(0, quantitiesSold, LAMBDA(a, b, a + b)), daysInInventory, LAMBDA(purchaseDate, saleDate, saleDate - purchaseDate), result, MAP(purchaseDates, saleDates, daysInInventory), result )