FIFO

[PhyuLayKhine333]

Hi All Experts,

I am beginner to BI and tried many methods in order to get the below result and also google about this problem but i did not get the correct answer and have no ideas how to use the relevant formula to get this below result.  

Any suggestion of fomula to get the beblow result?

Thanks and Regards,

Sales Table

Date	Product Name	Quantity
10/10/2022	A	10
10/12/2022	A	3
10/12/2022	A	2
10/13/2022	A	1
10/10/2022	B	22
10/11/2022	B	10
10/12/2022	B	3
10/12/2022	B	3

Purchase Table

Date	Product Name	Quantity	Cost
9/12/2022	A	10	110
9/30/2022	A	4	130
10/1/2022	A	6	150
9/1/2022	B	20	210
9/2/2022	B	20	250
9/20/2022	B	10	230

Then i would like get the table like this;

Date	Product Name	Quantity	Cost of sales	Closing Inventory Qty	Closing Inventory Value
10/10/2022	A	10	1100	10	1420
10/12/2022	A	3	390	7	1030
10/12/2022	A	2	280	5	750
10/13/2022	A	1	150	4	600
10/10/2022	B	22	4700	28	6800
10/11/2022	B	10	2500	18	4300
10/12/2022	B	3	750	15	3550
10/12/2022	B	3	750	12	2800

[pi_eye]

Hi 

Hi PhyuLayKhine333,

Please see .pbix https://drive.google.com/file/d/1WZ1oCP-BtqSxu8t672K1OyTCqUlEdNZv/view?usp=sharing

I was able to achieve this in Power query with a number of steps. Basically we to work out the cumulative number of units sold, and purchase for each product. Then we take that and make a range.

EG for products purchased, I_From and I_To are the cumulative range of total #items 

We do the same for the stock being sold. What we can then do is compare those ranges.

So for the 4 units of stock bought at 130 - any stock we sell will between the 11th and 14th items sold.

Those two tables can then be merged and filtered to retrieve only the matching rows where there is overlap between these ranges. These overlaps can then be priced as per the purchased table.

This table can be further refined and CoGS added:

Let me know if you can access the .pbix. There are a lot of steps!

Pi

[PhyuLayKhine333]

Thank you so much for your precious reply and also provide the files. But one of my questions is that the Product A numbers are accurate. But the numbers for Product B are wrong . My data include multiple products and multiple sales and purchase transactions per day . Could you please give me the suggestion about how to correct the Product B 's Cost of Sales. 

Thanks and Regards,

Also ,I can access .pbix。

[pi_eye]

Thank you, I will take a look later. It could also be that I copied the initial data wrong but will take alook.

[PhyuLayKhine333]

For the first product A , it got the correct answer . But , for the products below Product A got the wrong answer . 

I have Multiple products , Multiple date and will have duplicate quantity and sale transactions for the same day . I am afraid that duplicate quantity will not display correctly. 

Thank you and Looking forward to your reply.