Forum Discussion

SimoneBraccio's avatar
SimoneBraccio
Copper Contributor
Dec 17, 2024

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
    )

     

Resources