Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello All,
I am trying to create a power bi to analyse loan options. I have the following measures: Opening Balance, Monthly Instalments, Capital Repaid and Closing Balance Interest Charged. I am having a circular dependency error because:
I do understand why that causes an error. I am just wondering how could I avoid the error. In my mind, there should be away since the circular dependency only occurs if Opening Balance uses the same row data when evaluating Capital Repaid. So far this is what I have:
_Interest Charged = [_OpeningBalance]*[x_piRate]/1200
_Capital Repaid = MonthAmort[_Monthly Installements] - MonthAmort[_Interest Charged]
_OpeningBalance =
_Loan[Loan Value]
- CALCULATE (
SUMX ( MonthAmort, [_Capital Repayment] ),
FILTER (
MonthAmort[paymentDate],
MonthAmort[paymentDate] < MAX ( MonthAmort[paymentDate] )
)
)
I got this down easily in excel, since you can evaluate the previous row quite straight forward:
Repayment Number | Opening Balance | Monthly Installments | Interest Charged | Capital Repaid | Closing Balance |
1 | 540,000.00 | 2,332.37 | 1,435.50 | 896.87 | 539,103.13 |
2 | 539,103.13 | 2,332.37 | 1,433.12 | 899.25 | 538,203.88 |
3 | 538,203.88 | 2,332.37 | 1,430.73 | 901.64 | 537,302.24 |
4 | 537,302.24 | 2,332.37 | 1,428.33 | 904.04 | 536,398.20 |
5 | 536,398.20 | 2,332.37 | 1,425.93 | 906.44 | 535,491.75 |
6 | 535,491.75 | 2,332.37 | 1,423.52 | 908.85 | 534,582.90 |
7 | 534,582.90 | 2,332.37 | 1,421.10 | 911.27 | 533,671.63 |
8 | 533,671.63 | 2,332.37 | 1,418.68 | 913.69 | 532,757.94 |
9 | 532,757.94 | 2,332.37 | 1,416.25 | 916.12 | 531,841.82 |
10 | 531,841.82 | 2,332.37 | 1,413.81 | 918.56 | 530,923.26 |
The reason I am using measures, in case it is relevant, is because I want to dynamically change the Loan, Rate and Loan Period base on "What If" sliders. In that way, I can dynamically explore different scenarios.
Hopefully, it is something that can be done. Any idea?
Hi @ooramirez ,
Please check if this interests you.
https://community.powerbi.com/t5/Desktop/Mortgage-Payment-Calculation/td-p/820036
https://forum.enterprisedna.co/t/create-amortization-table/2404/3
Regards,
Harsh Nathani
User | Count |
---|---|
50 | |
49 | |
20 | |
17 | |
16 |
User | Count |
---|---|
110 | |
53 | |
42 | |
26 | |
22 |