Complicated formula

[didijaba]

Hello,
in the Excel data model I need to calculate the interest that becomes payable when the principal of a loan is partially repaid, where: Payable interest = (Total interest accrued to date - Any amount already payable) x Repaid amount / Outstanding amount before repayment The problem is when there are a series of repayments. I am using Power Pivot DAX and I get a circular reference error when trying to subtract the sum of the amounts already payable. I have the repayment number, the total interest accrued to date and the ratio of the repaid amount to the outstanding amount before repayment as three columns of my Excel data model. I think the problem in mathematical terms looks like the following, where Ax is the amount of interest accrued at the date of the xth repayment, and rx is the ratio of repaid amount to outstanding amount before repayment: Repayment number Interest accrued Ratio of amounts Payable interest 1 r1 A1 r1 x A1 2 r2 A2 r2 x (A2-r1A1) 3 r3 A3 r3 x (A3-r2(A2-r1A1)-r1A1) 4 r4 A4 r4 x (A4-r3(A3-r2(A2-r1A1)-r1A1)-r2(A2-r1A1)-r1A1) etc. etc. What would be the formula for a calculated field in Power Pivot that gives the Payable interest given the first three columns? Please look at the attached file, basically I need a calculated column in the DAX data model (Table1) which gives the same result as the yellow F column in the Excel table (interest payable), using only data in columns A to EGrateful for any help
https://www.dropbox.com/s/vidl37sweyxexvm/Sample loan data.xlsx?dl=0

 

[scottsen]

Ya, this is where you wish there was a MULTIPLYX, so you don't have to use freaky SUMX/EXP/LN maths. On the bright side your subject is very accurate Last time I had a similiar problem, I near gave up... but ended up reaching out to rob at powerpivotpro, who was able to think through it. It wasn't fun times... but given how far you have already come in your thinking (based on your calc columns in the model), I think you *can* get there... but ugh.

 

[didijaba]

I am stuck

 

[didijaba]

I tried to ask rob at powerpivotpro, as you said. Thanks for advice.

 

[didijaba]

Ok, I have solved it. Thanks for your replay

 

[didijaba]

Sorry, I have missed something, my solution is no good. I need to use sum from earliest data to earlier data in same calculated column using dax. How to do this?

 

[didijaba]

I was answered at Understanding Circular Dependencies in Tabular and PowerPivot – SQLBI). And to explain it a litlle more see https://cwebbbi.wordpress.com/2011/05/01/recursive-calculation-problems/