Stock Portfolio Control ! Can i use the column that I am creating on the same calculation?

[felipecarvalho]

I Want do to a calculation that can track the Median Acquisition  (better explaining in the next reply) cost of my stocks.

This number should be only affected by my Buys!  

When I sell the Stock , this number is not affected( what happens is that the quantity is subtracted and sum of the cost should be adjusted, so that the result of the division is the same as before the sale.

I put the inputs and the Expected results above.

The major problem that I am struggling is that it seems that I need to create a column that refers to the previous Value of that same column that I am creating on the same Step... 
I can do it in Excel , but it seems I cand do the same In PowerQuery... or i am missing som

[Anonymous]

Bom dia!
Felipe conseguiu resolver, estou exatamente com a mesma duvida dua, ja fiz N contas, e medidas.

Encontrar o custo medio ponderado é tranquilo, e que tambem não estou conseguindo é como dar baixa na saida, reduzindo a quantidade de produto e o custo acumulado de acordo com o custo medio apurado.

[felipecarvalho]

@Anonymous  sim a dificuldade é a mesma. 

Se você quer baixar o estoque, você precisa saber o custo do estoque anterior... 

O que aconteceu desde a minha última atualização :

1) Eu coloquei essa questão no WORKANA para ver se alguém lá conseguiria resolver eu pagando...

2) Enquanto isso, eu me rendi e passei por essa etapa trazendo os dados para uma tabela de Excel. Ai no Excel eu usei fórmulas para conseguir resolver... Em um contexto de excel onde podemos referenciar a celula acima da mesma coluna não foi muito difícil não... 

Estou finalizando o projeto, fazendo esse misto de PowerQuery e Excel. Precisei colocar uma aba auxiliar de pré-resultado para conseguir tratar os casos de transferência de corretora.

A pessoa que se interessou pelo projeto no Workana disse que conseguiu resolver, mas o valor que ele está me cobrando é um pouco acima que eu achei que iria gastar com o processo ( além do que eu acabei resolvendo, não de forma tão elegante, a questão).
Se interessa por dividir esses custos?  Ai eu vejo a solução e te repasso o código.

[felipecarvalho]

someone?   I just start to learn about custom functions and while loops... but I still couldnt figure out the problem...

[v-kelly-msft]

Hi  @felipecarvalho ,

Loop calculation is not supported in dax,we only have workarounds for plus and minus calculations,not including multiplication and division.So in your requirement,if the column "Individual Cost" contains the same value,we may have a workaround to do the loop.

My suggestion is to finish the loop calculation outside desktop first then load the model to desktop to do further analyzing.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[felipecarvalho]

@v-kelly-msft   

First of all thanks for your reply!

I am not trying to solve this I'm powerBI , but in PowerQuery.

In order to automate the entire process I need to solve this with M code.

I am finding it very difficult but I think there is a way. 

One hint was this post about recursive functionalities:

https://community.powerbi.com/t5/Community-Blog/Fun-with-Graphing-in-Power-BI-Part-SQRT-POWER-SQRT-8...

but so far I am still in a dead-end...

[v-kelly-msft]

Hi  @felipecarvalho ,

Is your issue solved now?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[felipecarvalho]

@v-kelly-msft 

Thanks for your help, but I still could not make work. The results from the loops are not what I expected.. 
I have too many variables that iterate ... the examples are usually very simple and I still can make the connection ...

[v-kelly-msft]

Hi @felipecarvalho ,

Sorry I didnt notice it.

I found a vedio teaching how to do loop in power query which may help:

https://www.youtube.com/watch?v=mXbjoSg9ldo

Let me know if you are still frustrated.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[felipecarvalho]

Thanks for reply Edhans!

I will try to give more detail about the calculations.

I put some RawData in a google sheets so that you can see the formulas that I did to get the results :

https://docs.google.com/spreadsheets/d/1PFA5f9yeq8D02Hz4R6HVeFnemuQgYn5zuEidyzcfujY/edit?usp=sharing

I am struggling to do this in Power Query because I don't know how to use the Previous result( OF THE SAME COLUMN) in the formula of this same Column. Is that Possible in Power Query?

You are right. I don want the median value. Or the average. It was a problem in translation. This number is called "PM" in Brazil, and it is used to calculate some taxes that you have to pay in certain operations in the Stock Market.

I think what I want is more similar to a weighted average of the cost ( with the rule that a sale should not affect the PM)

Let me give an example.

Day 1 - Buy 100 of Stock A . Price = 10. Quantity = 100 Cost = 10*100 =1000
Number of stocks = 100
PM ( what i am looking for) = Cost/number of stocks = 100

Day 2 - Buy 200 of Stock A. Price = 20 . Quantity = 200 Cost = 20 * 200 = 4000
Number of Stocks = 300
PM = (cost of buy day 1+ cost of buy day 2 ) / number of Stocks
(1000+4000) /300 =16,66666

Day 3 - Sale of 200 StockA ( the price of the sale does not matter).
Number of Stocks = 100
A Sale event does not change the PM!
I can just copy PM before the sale =16,666 or you can use the formula:
(
(Previous PM * Previous Number of Stocks ) +
( ( -Quantity of Sale) * Previous PM * )
) / number of Stock

= ((16,66*300 ) + (-200 *16,66) ) / 100
=16,666

I hope the example and the data in the link can clarify what I want.

Thanks for all the help!

Feel free to ask another question ( I am stuck with this problem for more than 15 hours...)

ps: The data has more stocks. They are classified by stock ( all the operations from one stock appear before the next stock). I Think just the raw data is enough so I can apply the solution.

[edhans]

This is best done in DAX, but you are going to need to share some data, and explain your expected results. Median returns the value in the middle. Do you mean average?

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[felipecarvalho]

Hello Edhans, thanks for your reply.

I will try to time more details, but I also put a sample of the data on this GoogleSheets Spreadsheet so you can see the calculations that are being made : 

https://docs.google.com/spreadsheets/d/1PFA5f9yeq8D02Hz4R6HVeFnemuQgYn5zuEidyzcfujY/edit?usp=sharing

In Brasil, in order to pay your taxes, you have to know your Cust of acquisition of your stocks.

I was not very accurate in the Translation.  It is not the median or a simple average. 

I think the term that best describes what I am looking for is a weighted average of the costs ... but with a few rules.

Let's go to the example ;

Day 1 :  Buy 100 of stock A    for 10 .  Cost= 10 * 100 = 1000 .  Quantity =100.

Number of stock in portfolio =100

 PM ( what I am looking) = Cost/Quantity = 10

Day 2 : Buy 200 of stock A for 20 . Cost =20*200 = 4000 . Quantity =200 .

Number of stock in portfolio =300

PM =  (Previous Pm* Previous Number of stock) + (Cost  ) / number of stock

(( 100  *100 ) +4000 ) / 300    = 16,666

Day 3 

Sale of 200  of stock A.    THE PM is not affected by sales. Now you have 100 stocks in your portfolio with a PM of 16,666 . 

On this line, you can just repeat the Previous Pm or you can do the calculation:

PM = ( (Previous Pm* Previous Number of stock) + ( (- Quantity sold) * Previous Pm) /Number of stock

     ( (16.66 * 300) + (-200 *16,66)) /100

 PM = 16,66

I hope it has all the details now. I think the link with the date makes it easier to follow.

I need to do this only in PowerQuery .

The raw data has more rows with different stocks but they are in order ( first will appear all the operations of one stock before the next. I think with just this dataset is possible to help me.

Final question just to be sure. I cant refer to the same column that I am creating on the same step right?

Thank you very much.