Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
If you look carefully at the Excel file, you will see the *NEW* average inventory price after the new purchase at higher price.
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!
Any thoughts?
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:
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:
I apologize for earlier inconvenience. Now it should be clearer.
Thanks in advance!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
65 | |
50 | |
45 | |
20 | |
17 |