Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
SonLeThai
Frequent Visitor

Weighted Average Cost of Goods Sold Help!!!!!

Hello Everyone,

Please does anyone have a sample of how to calculate the Weighted Average Cost of Goods Sold where there are

Open Inventory
Inventory purchased monthly
Monthly sales for each product

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.

I search a lot but can't solve my problem

Please help me.

Eg:

SonLeThai_0-1695898309094.png

Pic is way to calculate Weighted Average Cost of Goods Sold.

Thanks

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@SonLeThai , to build one hand you have to use cumulative measure

 

example

 

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

 

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

 

check you share weighted Avg formula

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@SonLeThai , to build one hand you have to use cumulative measure

 

example

 

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

 

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

 

check you share weighted Avg formula

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Tks you @amitchandak  your susget give an idea to solve my solution.
that's great!!!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.