Forum Discussion
Using previous row result
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?
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 ReturnBe aware with the function name, it need to be called inside the function as well.
Also, create a custom column to call the function.
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
3 Replies
camargos88Community Champion
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 ReturnBe aware with the function name, it need to be called inside the function as well.
Also, create a custom column to call the function.
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
- v-diye-msft
Community Support
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!
