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.
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!