Calculate Stock Day to Day Quantitative Balance

[George1973]

Hi All,

I have a following sample table:

Where I have to calculate weighted cost per item based on the purchased qn_ty and purcahsed orders amounts. 

I need to create to table in Power BI, where as a result I would have Yellow column (Weighted cost) and Gray Column (End Qnty). All other data are available.

Here is the dropbox link to the excel file:

https://www.dropbox.com/s/5av0eqpe3geyn3x/COGS_Calculae%20Template.xlsx?dl=0

Here is the data:

Oper Date	Prod Code	Qnty Tot Received	Received Price	Cost Tot Received	Weighted Cost PCE	Qnty Sold	COGS	End Qnty
01-01-23	001	10	10	100	10.00	3	30.00	7
02-01-23	001	7	9.9	69.3	9.95	2	19.90	12
03-01-23	001	5	9.8	49	9.91	1	9.91	16
04-01-23	001	6	10	60	9.93	5	49.66	17

I'm sure that I should use ERLIER function, but HOW I faill to realize 😞

Thanks in advance for your help.

[andhiii079845]

I hope this are the underlaying data?
Weighted cost = SELECTEDVALUE(table[Cost])-SELECTEDVALUE(table[Cost])
The same way for the second measure.

[George1973]

Hi @andhiii079845 ,

Thanks for your respond.

I think there is a misunderstanding here and it is totaly my fault if it is so.

I beleive the result lays in much more complex solution, then you've suggested. If use the provided link and look at the excel template, you will understand , what I mean.

The "End Quantity" and then even more complex "Weghted Cost" have to be requiring from the previous row content.

- End Quantity should be calculated as "Running Total" of Qnty-Received minus Running Total of "Qnty-Sold"

I worked hard on it yesterday and got the desired result:

Where 1st and 2nd collumns - CLMN_Purcahsed Qnty and CLMN_Total_Usage are Total Running sum from Qnty Received and Qnty Sold (Out) respectivly and finaly I got CLMN_Endig Stock as the desired result. Here is the code:

CLMN_ENDING_STOCK = 
VAR ActualCode=TS_COGSS[GOODS_CODE]
VAR InTot=CALCULATE(sum(TS_COGSS[CLMN_Purchased_Qnty]),FILTER(TS_COGSS,TS_COGSS[Action_Date]<=EARLIER(TS_COGSS[Action_Date]) && TS_COGSS[GOODS_CODE]=ActualCode))
VAR OutTot=CALCULATE(sum(TS_COGSS[CLMN_Total_Usage]),FILTER(TS_COGSS,TS_COGSS[Action_Date]<=EARLIER(TS_COGSS[Action_Date]) && TS_COGSS[GOODS_CODE]=ActualCode))
Return
InTot - OutTot

So, fist task is over. But now I'm facing the main problem: How to calculate "Weighted Cost" and I fail to do it?

Weighted  Cost - If we have the first day, the result is very simple - Just [CLMN_Purcahsed_Amount] / [CLMN_Purchased_Qnty], but when we come to the next day and after, then:
( [CLMN_PrevDay_Stock]*[CLMN_PrevDay_WeightedCost] +[CLMN_Purchased_Amount] )
/
([CLMN_Purchased_Qnty + CLMN_PrevDay_Stock])

This is the logic and formula. Unfortunately, I'm missing only one component (The Red Highlighted Number).. And I can not understand how can I get it 🙂

Hope, this explanation will help to understand the issue more exactly.

Regards,

George

[andhiii079845]

This was my short question: This are the underlaying data, or? 😉

Please upload some new example. I do not want to copy it from a screenshot.