Simple recursive calculation - Power Query

[brunoiadocicco]

Hello,

I'm trying to build the following calculation in Power Query:

d	t	p	t' = t x s (d-1)	s = s (d-1) + p + t'
0		-		3.600.000
1	1,20%	(200.000)	43.200	3.443.200
2	0,60%	(300.000)	20.659	3.163.859
3	0,40%	(700.000)	12.655	2.476.515
4	0,50%	(650.000)	12.383	1.838.897

Values in black are inputs and values in red are the results I want to get.

In excel is very simple, basically I want to get the previous row (s{d-1}), calculate the correction (t' = s{d-1} x t) and then calculate the next s (s{d-1} + p + t'), and repeat. But, I'm having problems building this logic into M language in Power Query. Someone could help, please?

[Greg_Deckler]

@brunoiadocicco Last time I was messing around with recursion in Power Query, it had to do with fractals. Attaching PBIX below. Here is a link to the articles. The articles have links to download additional PBIX file examples. Should be something in there to help your particular situation, I think I did a different solution for recursion in each article. 

https://www.linkedin.com/pulse/fun-graphing-power-bi-part-3i-greg-deckler-microsoft-mvp-/?msgConvers...

https://www.linkedin.com/pulse/fun-graphing-power-bi-part-sqrtpowersqrt82-deckler-microsoft-mvp-/?ms...

https://www.linkedin.com/pulse/fun-graphing-power-bi-part-5-by-5-greg-deckler-microsoft-mvp-/?msgCon...

[brunoiadocicco · Accepted solution]

@Greg_DecklerHello Greg

Thank you for your reply and for sharing those articles, it help me a lot!

Still struggle a little to refer the values I wanted to use in calculations, but I manage to solve this problem creating a new column of lists instead (if you know a better way to do it, I would love to hear it).

Here is the code I used, in case someone else want to know:

let
    fxBalance = (InitialValue,Payments,InterestRate,Counter,Index) =>

    let

    Correction = if InterestRate{Counter-1} = null then 0 else InitialValue*InterestRate{Counter-1},

    FinalBalance = if InitialValue - Payments{Counter-1} + Correction < 0 then 0 else InitialValue - Payments{Counter-1} + Correction,

    Return = if FinalBalance < 0 or Counter >= Index then FinalBalance else @fxBalance(FinalBalance,Payments,InterestRate,Counter+1,Index)

    in

    Return

in
    fxBalance

Using the function:

Table.AddColumn(#"Personalização Adicionada6", "Personalizar.1", each fxBalance([Initial Value],[Payments List],[Interest Rate List],1,[Index]))

The results:


ty