Partially Weighted and FIFO method for inventory cost calculation

[George1973]

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] (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):

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:

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]))

[AllisonKennedy]

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]

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:

The current formula works in this way: [Total Purchased Costs] / [Total Porchaed Quantity] = 8.15 (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:

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

[AllisonKennedy]

@George1973
sorry, 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.

[George1973]

@AllisonKennedy Thanks a lot for the comment.

I will try to apply the EARLIER finction as you've suggested and report back about the results

[George1973]

@AllisonKennedy 
Hi,

I tried EARLIER as suggested, but did not work as "measure". As for the calculated column I do not want to do it, because there are millions of records in that data set one more calculated column will make it heavier.

I’m thinking about another solution. Not done yet 😞

[AllisonKennedy]

@George1973

You should be able to get similar results using variables in a Measure as what the EARLIER function will do with calculated column.

You just need to be careful when you define the VAR so that it has the necessary row context that you are looking for.

[amitchandak]

@George1973 , red columns are the one need to calculate? Costing needs to be done based on the stock rate assuming FIFO?

In case you have not referred it

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

[George1973]

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