Partially Weighted and FIFO method for inventory cost calculation

Question

Hi All,I'm stucked with the following DAX task:I have a table and measures calculating the quantitative stock at the end of the desired period (please see the pic. below):Everithing works fine except the Stock Cost for the end of the period (Yellow Column).If it were a training or a simple dataset I would say that it is OK - Because the current measure calculates the stock values with the following approach:COST OF GOODS = DIVIDE([TOTAL CUMM COST GOODS RECEIVED],[TOTAL GOODS CUMM RECEIVED],0)and then:Cost of End Stock for Period = [COST OF GOODS]*[Stock End for Period]&nbsp;(This is actualy what is written in the yellow column)..and it works fine.. Meaning The Cost for the period end should be equal to the value of TOTAL COSTS of Good Received divided by TOTAL QNTY of Good Received - Theoreticaly!But in reality we have another picture:Imagine we have the following case (I have layed in out in excel for more clearity):&nbsp;&nbsp;When we come to the date5 (highlighted in green) to sell 150 untints of prod1, there should be calculation considering not only the total weighted average of the total IN OUT, but also partially also FIFO method like given below:&nbsp;Meaning to calculate the total cost of the remaining batches and summirize it with the upcomming new batch by weighted average. Ultimatly wich will be the running cost of the 150 selling units on the date5.In excel it's very easy to do this calculation and table - just pointing the running cost of the selling units a row before the transaction, but I can not do it in DAX and Power BI accordingly 😞Any suggestions and comments will be very mych appriciated.Thanks in advance,BTW: The date filter for the accumulated sums looks as follows:FILTER(ALL(DateKey),DateKey[Date]=MAX(TS_OPERATIONS[OPERDATE]))&nbsp;

allisonkennedy · Answer

One way to reference a previous row in DAX Is to create an INDEX column, then use the EARLIER function to reference the current row INDEX value -1 using FILTER(table, EARLIER(table[index])+1 = table[index])

I don't fully understand what the problem is though, why just the green highlighted and not all goods sold? What rows/columns are you in when calculating E21/C21?

george1973 · Answer

Hi,I'm back again and sorry for the late answer.I'm providing the table showing the both results - The current one (Red Numbers) which works in DAX and I'm not fully satisfied and the disired one (Blue Numbers) - How the DAX should work:&nbsp;The current formula works in this way: [Total Purchased Costs] / [Total Porchaed Quantity] = 8.15&nbsp;(Emphsizies all the date periods from Date1 to Date10) - That's why all the COGS (Cost of Goods Sold have the same costs - 8.15).If you notice - When if the quantitative stock goes to zero, the both variants end up with the zero volume balance as well. But what is important to me - Are the intermediate COGS and Stock Balances within the Dateperiods:DateProductBuy_QntyBuy_CostBuy_AmountSell_Qnty1.Sell_Amount1.COGS2.Sell_Amount2.COGSStock_Qnty`.Stock_Unt_Cost1.Stock_Amount2.Stock_Unt_Cost2.Stock_AmountDate1Prod1100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.15100&nbsp;&nbsp;&nbsp;&nbsp; 10.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10.000&nbsp;&nbsp;&nbsp;&nbsp; 1,000.00Date2Prod150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 600.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.15150&nbsp;&nbsp;&nbsp;&nbsp; 10.667&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,600.00&nbsp; &nbsp; &nbsp; &nbsp; 10.667&nbsp;&nbsp;&nbsp;&nbsp; 1,600.00Date3Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 320.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.67&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 244.62&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.15120&nbsp;&nbsp;&nbsp;&nbsp; 10.667&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,280.00&nbsp;&nbsp;&nbsp;&nbsp; 11.295&nbsp;&nbsp;&nbsp;&nbsp; 1,355.38Date4Prod1200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,200.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.67&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.15320&nbsp;&nbsp;&nbsp;&nbsp; 10.875&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,480.00&nbsp;&nbsp;&nbsp;&nbsp; 11.111&nbsp;&nbsp;&nbsp;&nbsp; 3,555.38Date5Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,631.25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.88&nbsp;&nbsp;&nbsp;&nbsp; 1,223.08&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.15170&nbsp;&nbsp;&nbsp;&nbsp; 10.875&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,848.75&nbsp;&nbsp;&nbsp;&nbsp; 13.719&nbsp;&nbsp;&nbsp;&nbsp; 2,332.31Date6Prod1300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,500.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.88&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.15470&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,348.75&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.154&nbsp;&nbsp;&nbsp;&nbsp; 3,832.31Date7Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;400&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,850.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.13&nbsp;&nbsp;&nbsp;&nbsp; 3,261.54&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.1570&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 498.75&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.154&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 570.77Date8Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 498.75&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 570.77&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;Date9Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;Date10Prod1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;

amitchandak · Answer

George1973 , red columns are the one need to calculate? Costing needs to be done based on the stock rate assuming FIFO?
&nbsp;
In case you have not referred it
https://radacad.com/dax-inventory-or-stock-valuation-using-fifo

george1973 · Answer

Actually the red cells are the EXISTING numbers which should be replaced by the DESIRED BLUE cell numbers.&nbsp;

allisonkennedy · Answer

George1973sorry, i haven't had time to come back to this one in depth, but you could have a go using the EARLIER function (or variables if you prefer) to constrain the calculation to look at current row's date period only when doing the COGS calculation.

Forum Discussion

Partially Weighted and FIFO method for inventory cost calculation

8 Replies

Related content

Inventory and Stock balance calculation

Calculate a Running Daily Inventory

kusto error- partial query failure

Inventory Forecast

Inventory Management Dashboard

Date	Product	Buy_Qnty	Buy_Cost	Buy_Amount	Sell_Qnty	1.Sell_Amount	1.COGS	2.Sell_Amount	2.COGS	Stock_Qnty	`.Stock_Unt_Cost	1.Stock_Amount	2.Stock_Unt_Cost	2.Stock_Amount
Date1	Prod1	100	10.00	1,000.00					8.15	100	10.000	1,000.00	10.000	1,000.00
Date2	Prod1	50	12.00	600.00		-	10.00		8.15	150	10.667	1,600.00	10.667	1,600.00
Date3	Prod1			-	30	320.00	10.67	244.62	8.15	120	10.667	1,280.00	11.295	1,355.38
Date4	Prod1	200	11.00	2,200.00		-	10.67	-	8.15	320	10.875	3,480.00	11.111	3,555.38
Date5	Prod1			-	150	1,631.25	10.88	1,223.08	8.15	170	10.875	1,848.75	13.719	2,332.31
Date6	Prod1	300	5.00	1,500.00		-	10.88	-	8.15	470	7.125	3,348.75	8.154	3,832.31
Date7	Prod1			-	400	2,850.00	7.13	3,261.54	8.15	70	7.125	498.75	8.154	570.77
Date8	Prod1			-	70	498.75	7.13	570.77	8.15	0	-	-	-	-
Date9	Prod1			-		-	-	-	8.15	0	-	-	-	-
Date10	Prod1			-		-	-	-	8.15	0	-	-	-	-