Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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!!
Thanks a lot! A stupid question: how do I access the file that you attached? There seems to be multiple files to extract but I don´t see the PBIX.
Thanks a lot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |