How to Handle Circular dependency for Amortization Schedule

[Euto]

I am having problems generating the Begining and Ending balance for Armotization Schedule, as they both depend on each other to get the required result.

See the error:

DAX Measures
Begining Balance = 

Begining Balance = 
var __1stPeriodBeginingBalance = MAX( 'Table'[Amount Borrowed])
var __RemainingPeriodBeginingBalance = CALCULATE([Ending Balance], DATEADD(TableCalendar[Dates],-1,MONTH))

RETURN
IF(MAX(TableCalendar[Period]) = 1, __1stPeriodBeginingBalance, __RemainingPeriodBeginingBalance)

Ending Balance =

Ending Balance = 
var __1stPeriodEndingBalance = MAX( 'Table'[Amount Borrowed]) - [1 Monthly Principal]
var __RemainingPeriodEndingBalance = [Begining Balance] - [1 Monthly Principal]

RETURN
IF(MAX(TableCalendar[Period]) = 1, __1stPeriodEndingBalance, __RemainingPeriodEndingBalance)

Other Dax created and referenced
- Monthly Payment 

1 Monthly Payment = 
VAR __PresendtValue = MAX( 'Table'[Amount Borrowed])
Var __Rate = MAX( 'Table'[Rate])
Var __RateBy12 = DIVIDE(__Rate, 12)
Var __NPER = MAX( 'Table'[Period])

Return
PMT(__Rate, __NPER, -__PresendtValue,0)

- Monthly Interest 

1 Monthly Interest = 
VAR __PresendtValue = MAX( 'Table'[Amount Borrowed])
Var __Rate = MAX( 'Table'[Rate])
Var __RateBy12 = DIVIDE(__Rate, 12)
Var __NPER = MAX( 'Table'[Period])
Var __Period = MAX(TableCalendar[Period])

Return
IPMT(__Rate,__Period,__NPER,-__PresendtValue,0)

- Monthly Principal

1 Monthly Principal = 
VAR __PresendtValue = MAX( 'Table'[Amount Borrowed])
Var __Rate = MAX( 'Table'[Rate])
Var __RateBy12 = DIVIDE(__Rate, 12)
Var __NPER = MAX( 'Table'[Period])
Var __Period = MAX(TableCalendar[Period])

Return
PPMT(__Rate,__Period,__NPER,-__PresendtValue,0)

Background Tables on Power Query

Table Photo

M language to generate Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUjE0MNAxMDDQMzBQ0lEyNgORQI6hgYUxCAB5hvqG+kYGhpZwpomlUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Amount Borrowed" = _t, Period = _t, Rate = _t, StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount Borrowed", Currency.Type}, {"Period", Int64.Type}, {"Rate", type number}, {"StartDate", type date}, {"EndDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Days", each List.Dates
(
    [StartDate],
    Duration.Days([EndDate] - [StartDate]),
    #duration(1,0,0,0)
)),
    #"Expanded Days" = Table.ExpandListColumn(#"Added Custom", "Days"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Days",{{"Days", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "SOM", each [Days] = Date.StartOfMonth([Days])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [SOM] = true),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"SOM"})
in
    #"Removed Columns"

TableCalendar Photo

 M language to generate TableCalendar

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTLUN9Q3MjC0hDNNQExTAwMdAwMDEEvPyFRVKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Leased ID" = _t, StartDate = _t, EndDate = _t, #"Initial Loan Balance" = _t, #"Interest Rate" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Leased ID", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}, {"Initial Loan Balance", Int64.Type}, {"Interest Rate", Percentage.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Years", each Date.Year([EndDate]) - Date.Year([StartDate])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Days", each List.Dates
(
    [StartDate],
    Duration.Days([EndDate] - [StartDate]),
    #duration(1,0,0,0)
)),
    Days1 = #"Added Custom1"[Days],
    Days2 = Days1{0},
    #"Converted to Table" = Table.FromList(Days2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Dates"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "SOM", each [Dates] =
Date.StartOfMonth([Dates])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [SOM] = true),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"SOM"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Period", 1, 1, Int64.Type)
in
    #"Added Index"

RelationShip

Expected Result:

Please Note:

I have read the Circular depency article by Albertor Ferari, problem is still not fixed. As his explanation is more applicable to calculated column not Mesure. I am not using calculated columns.

[Anonymous]

Is there a way to make this work if the monthly payment is not the same every month?

Regards,

Sid

[Euto · Accepted solution]

@lbendlin @amitchandak @Anonymous  Thanks to you all for your responses.

I figured I had to go a completely different route to avoid the circular dependency by creating all independent measures.

Solution:

- I finetuned Other Dax created

Monthly Payment

Monthly Payment= 
VAR __PresendtValue = MAX( 'Table'[Amount Borrowed])
Var __Rate = MAX( 'Table'[Rate])
Var __AllPeriod = CALCULATE(MAX(TableCalendar[Period]), ALL(TableCalendar))

Return
PMT(__Rate, __AllPeriod, -__PresendtValue,0)

Monthly Interest

Monthly Interest= 
VAR __PresendtValue = MAX( 'Table'[Amount Borrowed])
Var __Rate = MAX( 'Table'[Rate])
Var __Period = MAX(TableCalendar[Period])
Var __AllPeriod = CALCULATE(MAX(TableCalendar[Period]), ALL(TableCalendar))

Return
IPMT(__Rate,__Period,__AllPeriod,-__PresendtValue,0)

Monthly Principal

Monthly Principal= 
VAR __PresendtValue = MAX( 'Table'[Amount Borrowed])
Var __Rate = MAX( 'Table'[Rate])
Var __Period = MAX(TableCalendar[Period])
Var __AllPeriod = CALCULATE(MAX(TableCalendar[Period]), ALL(TableCalendar))

Return
PPMT(__Rate,__Period,__AllPeriod,-__PresendtValue,0)

- I created a new Dax for the Begining and Ending Balance

Begining Balance

Begining Balance= 
VAR __PresendtValue = MAX( 'Table'[Amount Borrowed])
Var __Rate = MAX( 'Table'[Rate])
Var __Period = MAX(TableCalendar[Period])
Var __AllPeriod = CALCULATE(MAX(TableCalendar[Period]), ALL(TableCalendar))
Var __PMT = PMT(__Rate, __AllPeriod, -__PresendtValue,0)
Return
FV(__Rate,__Period-1,__PMT,-__PresendtValue,0)

Ending Balance

Ending Balance= 
VAR __PresendtValue = MAX( 'Table'[Amount Borrowed])
Var __Rate = MAX( 'Table'[Rate])
Var __Period = MAX(TableCalendar[Period])
Var __AllPeriod = CALCULATE(MAX(TableCalendar[Period]), ALL(TableCalendar))
Var __StartPeriod =CALCULATE(Min(TableCalendar[Period]), ALL(TableCalendar))
Var __CUMPRINC = -CUMPRINC(__Rate,__AllPeriod,__PresendtValue,__StartPeriod,__Period,0)

RETURN
__PresendtValue - __CUMPRINC

Outcome Of above dax
 

Thanks.
Eli

[Anonymous]

For such things to work you have to disentangle at least one of the measures (meaning: only one measure can depend on the other, they both can't depend on each other mutually). If value f(n) depends on g(n-1) and g(n) depends on f(n-1), then certainly f(n) depends on f(n-1) and/or f(n-2). So, you have to express f(n) in terms of f(n-1) and/or f(n-2) and you have to get rid of recursion. Then and only then will you be able to do the calculation.

[Euto]

Missing pictures from the problem above..

Picture of Error:

Tables relationsip

[lbendlin]

First of all, thank you for the details you provide. Makes it much easier to follow.

I agree that the beginning balance depends on the previous period's ending balance.  But please explain why the ending balance would depend on the beginning balance? You should not need that, rather just do a SUMX over the current period.

What's the purpose of your calendar table?  It looks incomplete.  You will want to have a table of contiguous dates that (just) covers your fact dates.

[Euto]

@lbendlin Thank you for your response, and the questions you have asked are very sensible.

Why the ending balance depend on the beginning balance ?

The Ending Balance = Beginning balance – Principal

• The beginning balance for the period 1 is known (the amount borrowed).
• However, the beginning balance for the Period 2 onwards (is not Known), as it is the Ending Balance of the Previous Period.

I am not sure how you mean by "do a SUMX over the current period". Can you please do this and see if you get the expected result as shown below?

Expected Result

What's the purpose of your calendar table?

To be honest at this point, the calendar table is not as useful, I could have easily created a period column on fact table given the one to one relationship.

But then when you think of it, even with the way it is, if I start generating, Quarters, Month Name or Days for further analysis it will become useful as I would not want to have those on the fact table.

But at this point, I am aware that it is not doing much in this test data.

[amitchandak]

@Euto , I have just seen it. Seem like other super users are already helping. Just see if these functions can help you. In case it does not resolve. I will look back

openingbalancemonth,openingbalancequarter, openingbalanceyear

https://youtu.be/6lzYOXI5wfo

closingbalancemonth ,closingbalancequarter, closingbalanceyear

https://youtu.be/yPQ9UV37LOU