Hi @aedniranao
Which three inventory costing methods do you have in mind ?
When searching at some explanations of Weighted Average COGS, there are multiple definitions : Periodic, Perpetual, also FIFO Perpetual and FIFO periodic exist.
My method presented was FIFO periodic average, not described
(some methods are explained in appendix I ).
Some comments on your DAX-code:
- It is hard, (not to say impossible) to comment on DAX code without knowing the (exact) context and the data-structure set up (e.g. do you have one input file or more, how are inventory value mutations affected by sales and purchases as you want inventory calculated on a WA-COGS method, etc.)
- Generally speaking, SUM is sufficient for a single column reference, you do not need SUMX, so Sum( All Brands Invoices [Total Qty]) would be sufficient.
- It is unknown how the "All Brands Invoices [Inventory Value] is calculated, with which values reduces the sales quantity the inventory value, see also point 1
- Comment on Rolling 12 COGS, see also point 1, formula seems not wrong.
Your question:
Summarily, all I want is a Dax formula for a weighted average cost of goods that is calculated at the end of every month for the company’s monthly closing.
To further help you with your question, a sample PBIX of your data is needed and a clear definition what method of WA COGS is used.
=====
A second workout below, now with the Periodic COGS method, hopefully helps you further, but be aware, the calculations are based upon 3 separate data input fact-files ( inventory, purchases and sales), which obviously affects the required DAX formulas to reach the desired output.
Please find attached an example of the Periodic COGS method worked out in Power BI, which is the easiest methodology.
Weigted Avg COGS (period).pbix (173.9 KB)
Hereby is COGS per unit from a period calculated as (total value inventory + total purchase value in the period) / (quantity inventory + quantity purchased in the period)
WA COGS (period) per unit =
Var PurchaseValue = SUMX( Purchases, [Qty purchased] * [Purchase price])
return
// Weighted Average COGS (period) per unit
DIVIDE( SUM(Stock[Begin Stock value]) + PurchaseValue, SUM(Stock[Begin Qty stock]) + sum(Purchases[Qty purchased]), 0)
See measure group “Measures Period WA COGS period”
An example report can be as follows :
Appendix I:
==> Period and Perpetual COGS explained
==>FIFO period COGS explained
Appendix II
P.S . For a better readability of the DAX code copied in the forum, you can use:
– copy in the code,
– select all code
—press then </>
—press thereafter " " , see also below picture and example of a much better readable result .
Quantites Supplied =
SUMX(‘All Brands Invoices’, ‘All Brands Invoices’[Total Qty])
