Computing First in First Out (FIFO) Cost of Goods Sold (COGS) & Inventory Report

[Anonymous]

Does anyone have dax / SQL program to compute COGS (Cost of Goods Sold) & Inventory based on First in First Out (FIFO) method in Power BI Report

Below is the example

Would appreciate support on this.

Details available in Data	Qty	Rate/Qty	Value	Remarks
Opening Stock	120	2	240
Purchase	200	3	600
Sales	220	5	1100
Details to be Computed in Power BI
Cost of Goods Sold (COGS):
Opening Stock_Lot consumed	120	2	240	Consumption first from Opening Stock using FIFO (First in First Out)
Purchase_Lot consumed	100	3	300	Balance Consumption from Purchase lot using FIFO (First in First Out)
Total COGS	220	2.45	540
Closing Stock	100	3	300	Opening + purchase-COGS

[amitchandak]

@Anonymous , refer if this can help

https://radacad.com/dax-inventory-or-stock-valuation-using-fifo

[ravigajjar_09]

https://radacad.com/dax-inventory-or-stock-valuation-using-fifo

This method is not useful for find cost. Data is coming incorrect

[Anonymous]

Hi Amit,

First of all thanks a lot for your reply. I checked out the link to solution. So the FIFO column that this solution refers is the gross margin (Sales value - cost arrived thru FIFO).

So can you tell me if I can also refelect 

a) The cost arrived thru FIFO

b) the inventory value at end of each day in this example

as I am attempting to have inventory dashboard with its value reflected. 

thanks

[Anonymous]

Hi @Anonymous ,

Please review the following links and check if they can help you achieve the requirements.

Share Valuation using FIFO method

Weighted Average Cost of Goods Sold

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with calculation logic of FIFO&COGS and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards