Perpetual Average Cost Inventory Method - Help Needed to Keep the Correct Average Inventory Price

[pravdon1]

Hi there!

I am trying to calculate my inventory totals using Perpetual AVCO method (https://xplaind.com/840515/avco-method). Everything is okay except one thing: I can not figure out how to keep the right Weighted Unit Cost price that would disregard any SALE transactions.

Down below please see attached the working files for more understanding.
XLS File 

PBIX  

What I am trying to achieve

If you look carefully at the Excel file, you will see the *NEW* average inventory price after the new purchase at higher price. This price to roll over

I basically need Power BI to automatically calculate that price and use it every time there is "BUY" in the "Buy/Sell" column.

Thanks in advance!

[pravdon1]

Any thoughts?

[v-mengzhu-msft]

Hi @pravdon1 ,

I would like to know what is the method you are talking about, which is "Perpetual AVCO"
Secondly, I'm sorry I don't quite understand your purpose, what kind of desired effect you are trying to achieve by "keep the right Weighted Unit Cost price that would disregard any SALE transactions".

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[pravdon1]

@v-mengzhu-msft ok, I have changed some parts. Please download PBIX and XLS files again. 
My main goal is to account for correct inventory value after each inventory purchse. Every time I purchase items, the average price of TOTAL inventory changes, but every time the sale is made, only Inventory Q-ty goes down and price stays the same until I purchase items again and it recalculates. I have done some explanation for you by using T Accounts in my Excel file:
T Accounts The main idea here is to create a column, where price of inventory updates every time the purchase is made, so whenever I sell inventory, i can keep track of the amount of Inventory Sold, Sales Amount and Revenue/Profit that is created in between. Here is a snippet of how I envision that:
The way it must look like I apologize for earlier inconvenience. Now it should be clearer.

Thanks in advance!