I need a DAX calculated column of the data model behind the attached Excel file that will reproduce the calculation of the yellow column of the spreadsheet data. If you open the data model with Power Pivot there will be a calculated column with 1's, and that is where the data should appear. You can use intermediate measures and columns to get to the result. Please post pictures of the data model measures and calculated columns, the contest is sealed so no-one else will see your entry. No macros please.
This result is needed 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 arises when there are a series of repayments, and you need to keep track of the amount already payable. I am using Power Pivot DAX and I get a circular reference error when trying to subtract the sum of the amounts already payable.
You will need Excel 2013 and PowerPivot add-in as well as Power View probably