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

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors