Avoid Circular Dependency when evaluating previous...

ooramirez · ‎05-27-2020

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:

Interest Charged is calculated using the opening balance (Interest Charged = Opening Balance * Interest Rate)
Capital Repaid is calculated using Monthly Instalments and Interest Charged (Capital Repaid = Monthly Instalments - Interest Charged )
Opening Balance is calculated using the cumulative of Capital Repaid evaluated in all previous rows (Opening Balance = Original Loan - Sum(all previous Capital Repaid)

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?

Anonymous · ‎05-30-2020

This smells of recursive calculations... which are NOT POSSIBLE in DAX. You have to find a closed formula for your amounts to be able to calculate like this. I know there are closed formulas so you have to find them and implement in DAX.

Best
D