Calculate days in stock based on stock value and ...

Ingo_BI2 · ‎06-02-2023

Hi,

Like the picture show I have two tables: Inventory transatctions (IT) and Inventory (I).

The IT table gives me Product, Date, Batch and Quantity while the Inventory gives Product, Date and Stock quantity on a given date.

I want to calculate the nr of days in stock based on this information and this is my proposed logic (assumption: FIFO)

For Product 5 we have recieved 2 batches (20 in Feb and 50 in May) and since we have a stock quantity of 60 on in June we have a movement of -10.

For Product 6 we have recieved 2 batches (100 and 120) and in June we have 100 in stock which equals a movement of -120.

For Product 7 we have recieved 2 batches (300 and 200) and in June we have 400 in stock which equals a movement of -100.

Based on the above I would like a table that give me Product, Batch, Stock Quantity. The Stock Quantity should be summarized on Product and Batch. The rule here is that I want the movement to start with subtract from the first batch and then move on to the other (and third, fourth etc etc) if "needed". And with needed I mean that if the movement is higher than the first batch the rest have to be removed from the second batch.

How would do to fix this kind of report? Do I need to create another table in the model to help me achieve this and what DAX code would you write?

NaveenGandhi · ‎06-04-2023

Hello @Ingo_BI2

Check the attached PBIX for the solution, Except "Days of Stock" all the other required column are calculated columns. I made a change to sample data for test case.

Play around and test the logic.

Let me know if this helps!

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!