The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello guys,
I am having difficulty in a calculation in DAX, the calculation is of weighted average cost of stock, here in Brazil we have many variations of cost, then this method is the most used.
Basically my problem is to calculate the average cost, because the end of a month is the base for the next one, then I find a kind of circular reference in this calculation.
In the image of the to see the formula that changes from line 1 to line 2.
This calculation is really giving me headaches, and I appreciate any help you can give me to solve this problem.
Best Regards.
Hi @Leandro
I have exactly the same issue that I am trying to solve. Did you ever get the average price to work with DAX? As I think you mentioned, it is simple with Excel but my data set as circa a million lines so running the system with excel formulas is not that effecient.
Kind regards,
Andrew
i Have created a Pbix file that will do waht your asking for.
it does it across a few colunms that are needed as you will need to do several look ups to refrence back to the previous month.
i have shared this file below
i apologise i did not have time to format the dax correctly and add comments but you should be ablet o follow the logic
PBIX FILE
Proud to be a Super User!
Thank you very much for you help and time, AnthonyTilley.
I have to analize very carefuly your pbix file. I coulnd't understand the logic at first time I read it.
And I can´t understand yet if it will do with a lot of products.
Thanks a lot for your time. I´ll give you feeedback as soon as possible.
Hi @Leandro,
Could you try the formula below to see if it works in your scenario?
Average Cost = VAR currentPeriod = Table1[Period] VAR minPeriod = CALCULATE ( MIN ( Table1[Period] ), ALL ( Table1 ) ) RETURN IF ( currentPeriod = minPeriod, DIVIDE ( Table1[Incial Total Value] + Table1[Input Total Value], Table1[Incial Quant] + Table1[Input Quant] ), DIVIDE ( CALCULATE ( SUMX ( Table1, Table1[Incial Total Value] + Table1[Input Total Value] ), FILTER ( ALL ( Table1 ), Table1[Period] < currentPeriod ) ) + Table1[Input Total Value], CALCULATE ( SUMX ( Table1, Table1[Incial Quant] + Table1[Input Quant] + Table1[Out Quant] ), FILTER ( ALL ( Table1 ), Table1[Period] < currentPeriod ) ) + Table1[Input Quant] ) )
Regards
Thanks a lot for your help, i tried the formula that you sent, but there is something that still not working, but i can see the metod is the right, setting "VAR" for the calculation to avoid circular relationships.
Maybe my description of the problem was not precise enough, so i posted the file on google drive, this way it can light the problem.
And again, thanks so much for the help, it will really make my job a lot easier.
@v-ljerr-msft Thanks a lot for your atention on this issue, seems like you're right, if in the future if a find some way to solve this, i'll let you know!
Thanks for your help!
Regards!
Hello Leandro!!
In meanwhile do you have you issue solved?
I have exactly the same need.
Greetings from Portugal.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
80 | |
78 | |
43 | |
37 |
User | Count |
---|---|
158 | |
111 | |
64 | |
59 | |
54 |