Using previous row result

[pedrocampos]

Hi

In this table presented below I have two columns, being: "Saldo_Inicial" and "Saldo_Final".

In the first row, the "Saldo_Final" column shows a value that was calculated from an initial value of 5000000 in the "Saldo_Inicial" column.
However, from the second line down I need the value shown in the "Saldo_Inicial" column to always be the calculated value in the "Saldo_Final" column of the previous line.

How do I do that?

[camargos88 · Accepted solution]

Hi @pedrocampos ,

You can create a recursive function to calculate all the "saldo_final" until the previous row like:

(_table as table, _saldo as number, _currentIndex as number, _index as number) as number =>
let
    Saldo = _saldo + (_table{_index}[Valor] + _table{_index}[Saldo Inicial]),
    Return = 
    if _index < _currentIndex - 1 then @Query1(_table, Saldo, _currentIndex, _index + 1)
    else Saldo
in 
    Return

Be aware with the function name, it need to be called inside the function as well.

Also, create a custom column to call the function.

[v-diye-msft]

Hi @pedrocampos 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

[camargos88 · Accepted solution]

Hi @pedrocampos ,

You can create a recursive function to calculate all the "saldo_final" until the previous row like:

(_table as table, _saldo as number, _currentIndex as number, _index as number) as number =>
let
    Saldo = _saldo + (_table{_index}[Valor] + _table{_index}[Saldo Inicial]),
    Return = 
    if _index < _currentIndex - 1 then @Query1(_table, Saldo, _currentIndex, _index + 1)
    else Saldo
in 
    Return

Be aware with the function name, it need to be called inside the function as well.

Also, create a custom column to call the function.

[pedrocampos · Accepted solution]

Hello.

THank you very much.

Below, I post the solution that I wrote based on your post.

(_table as table, _saldo as number, _currentIndex as number, _index as number, _index_anterior as number) as number =>
let

Saldo = (_saldo * (1+(_table{_index_anterior}[Indexador_valor])))-((_saldo * (1+(_table{_index_anterior}[Indexador_valor])))/(_table{_index_anterior}[N_Parcela])) + _table{_index}[Saldo_Partida],

Return =
if _index < _currentIndex - 1 then @Saldo_inicial(_table, Saldo, _currentIndex, _index + 1,_index)
else Saldo
in
Return