cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Euto
Frequent Visitor

How to Handle Circular dependency for Amortization Schedule

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

Table.png

 

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
TableCalendar.png

 


 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:

Expected Result.png

 


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.

 

1 ACCEPTED SOLUTION
Euto
Frequent Visitor

@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
 Outcome.png

 

Thanks.
Eli

View solution in original post

7 REPLIES 7
Phogon
Helper II
Helper II

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

 

Regards,

Sid

Euto
Frequent Visitor

@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
 Outcome.png

 

Thanks.
Eli

Anonymous
Not applicable

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
Frequent Visitor

Missing pictures from the problem above..

 

Picture of Error:

Circular Dependency Error.png

 

 

Tables relationsip
Table to TableCalendar Relationship.png

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
Frequent Visitor

@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

Expected Result.png

 

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.

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!