How to Handle Circular dependency for Amortization Schedule

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