cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pravdon1
Frequent Visitor

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

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 achieveWhat 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 overThis 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!

3 REPLIES 3
pravdon1
Frequent Visitor

Any thoughts?

v-mengzhu-msft
Community Support
Community Support

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

 

@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 AccountsT 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 likeThe way it must look like I apologize for earlier inconvenience. Now it should be clearer.

Thanks in advance!

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors