cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
obartoli
Frequent Visitor

Weighted Moving Average

Weighted Moving Average

Hello.

Personal, my doubt is how to calculate the weighted moving average, in Power Bi, the problem is when I have to calculate the cost of the unit sold, 
multiplying by the average cost, and deducting this value from the Total. Link with template file:
https://drive.google.com/drive/folders/1F4X244y3NXB-hnM8rOuqE1UMLVU_3msC?usp=sharing
Example: Initial = 100 Units of Product X, at the cost of R$ 500.00 per batch Acquisition of 200 Units of Product X, at the cost of R$ 1,150.00 per lot The average cost of Product X will be: BRL 500.00 + BRL 1,150.00 divided by (100 + 200) units = BRL 1,650.00: 300 = BRL 5.50 per unit In the effective sequence to a write-off of -120 units = 120 multiplied at the cost of R$ 5.50 = -660 Accumulated cost goes from R$ 1650.00 - 660 = 990 and the Total of 300 units - 120 = 180, maintaining the average cost, until a new acquisition. In the example below, where it is 7.19 the correct is 5.81, which would be R$ 1650.00 - 660.00 +1800 = 5.81, if there is no sale Works perfectly.

 

obartoli_0-1631665686418.png

 

 

 

6 REPLIES 6
daxer-almighty
Solution Sage
Solution Sage

Your visual does not look like the one you've shown above:

daxeralmighty_0-1631663412391.png

 

I adjusted, because I had improved the formula. Thanks

I don't understand now... You've improved the formula, so it is already working fine?

No, it's still wrong, note in the image I updated, the correct result is from excel.

obartoli
Frequent Visitor

Can anyone help me with this question?
daxer-almighty
Solution Sage
Solution Sage

@obartoli 

 

Would you please format the post in a convenient way to read it? Currently it requires people to scroll across and is not very readable. The font is weird as well. Thanks.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.