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 the relevant formula to get this below result.  

 

Any suggestion of fomula to get the beblow result?

 

Thanks and Regards,

 

Sales Table

DateProduct NameQuantity
10/10/2022A10
10/12/2022A3
10/12/2022A2
10/13/2022A1
10/10/2022B22
10/11/2022B10
10/12/2022B3
10/12/2022B3

 

Purchase Table

DateProduct NameQuantityCost
9/12/2022A10110
9/30/2022A4130
10/1/2022A6150
9/1/2022B20210
9/2/2022B20250
9/20/2022B10230

 

Then i would like get the table like this;

 

DateProduct NameQuantity

Cost of sales

Closing Inventory Qty

Closing Inventory Value

10/10/2022A101100101420
10/12/2022A339071030
10/12/2022A22805750
10/13/2022A11504600
10/10/2022B224700286800
10/11/2022B102500184300
10/12/2022B3750153550
10/12/2022B3750122800
Need Help

4 Replies

  • 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。

      • pi_eye's avatar
        pi_eye
        Resolver IV
        3 years ago

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