Forum Discussion

PhyuLayKhine333's avatar
PhyuLayKhine333
Helper I
3 years ago

FIFO

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 th...
Need Help
pi_eye's avatar
pi_eye
Resolver IV
3 years ago

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's avatar
PhyuLayKhine333
Helper I
3 years ago

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。