Forum Discussion
Weighted average recalculation after change
Hello, could you please help me with writing dax formula that would show new weighted average after every inventory change?
I have an opening quantity for each product and its value for every year in one table.
Then I have another table with every supply increase - quantity and price. And also for every supply sell - quantity.
Sold inventory is valued by last known weigted average.
I want to write a formula for calculated column thath would recalculate weighted average price after every supply increase. The formula should work like this:
((Last known inventory quantity-sold quantity between the date of this supply increase and the previous one)*last known weighted averate+supply increase quantity*its price)/(last known inventory quanity - sold quanity + supply increase quantity)
I don't know how to incorporate the first table for the initial value.
Thank you for all your help.
Hi LH22 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Click "Transform Data" to enter the Power Query editor to add index column.
(3)We can create a calculated column.
Column = var a=CALCULATE(MAX('Table'[Weighted average price after supply increase]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1)) var b=(('Table'[Last known inventory]-'Table'[Sold products from last supply increase])*a+'Table'[Supply increase]*'Table'[Supply increase price])/('Table'[Last known inventory]-'Table'[Sold products from last supply increase]+'Table'[Supply increase]) return IF('Table'[Index]=1,'Table'[Weighted average price after supply increase],b)(4) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Basically I need this.
Any help would be appreciated.
Hi LH22 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Click "Transform Data" to enter the Power Query editor to add index column.
(3)We can create a calculated column.
Column = var a=CALCULATE(MAX('Table'[Weighted average price after supply increase]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1)) var b=(('Table'[Last known inventory]-'Table'[Sold products from last supply increase])*a+'Table'[Supply increase]*'Table'[Supply increase price])/('Table'[Last known inventory]-'Table'[Sold products from last supply increase]+'Table'[Supply increase]) return IF('Table'[Index]=1,'Table'[Weighted average price after supply increase],b)(4) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
