cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors