topic Re: Computing First in First Out (FIFO) Cost of Goods Sold (COGS) & Inventory Report in DAX Commands and Tips

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

Anonymous — Tue, 03 May 2022 09:24:31 GMT

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

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

amitchandak — Tue, 03 May 2022 12:31:19 GMT

Anonymous , refer if this can help

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

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

Anonymous — Wed, 04 May 2022 12:23:44 GMT

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

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

Anonymous — Fri, 06 May 2022 06:12:50 GMT

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

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

ravigajjar_09 — Thu, 29 Aug 2024 10:29:01 GMT

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

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