How to calculate amortised cost in power BI

[Anonymous]

Hello PBI Expert,

I'm facing a problem with the calculation of the cost amortised for a loan. The final objective is to retrieve the accounting records by date. Here an Example of what I'm trying to do.

1	A	B	C	D	E	F	G	H
2	Date	Difference in Day	Nominal Loan	Amortised cost value	Amortised cost interests	Effective interest	Refund	Cash flow
3	07/04/2019	0	51.428.571	51.000.907	0
4	30/06/2019	84,00	51.428.571	=D3+E4+H4	=D3*(1+1,30%)^(B4/365)-D3	-           119.658	-
5	30/09/2019	92,00	51.428.571	=D4+E5+H5	=D4*(1+1,30%)^(B5/365)-D4	-           131.054	-
6	07/10/2019	7,00	47.142.857			-               9.972	-  4.285.714	-                                                                               4.546.398

I have highlighted in orange the two fields that I'm trying to calculate and in red the excel formula. 

I know that is not possible in Power BI to make a recursive calculations and i cannot reference the same field. Do you have some tricks? Also using M?

Best

[Anonymous]

Hi @Anonymous ,

If my post helps you a little ,please kindly Accept it as the solution to make the thread closed. More people will benefit from it.

Best Regards,
Eyelyn Qin

[Anonymous · Accepted solution]

Hi @Anonymous ,

Yes. We usually need to use M language to do recursion calculations.

Please refer to
Recursive Functions in Power BI / Power Query

Recursive Functions in Power Query - XcelanZ

Best Regards,
Eyelyn Qin

[Anonymous]

Thank you very much! It's what I'm looking for!