- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- loan amortization?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

mrothschild

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-26-2019
11:51 AM

This seems like it should be much easier than I've found it to be.

I have a table that has Columns A, B, and D below and I'm trying to calculate C & E.

C = prior row's E and E = current row C + current row amortization.

I'm less worried about the last period Amortization [Column D], as I think I've already figured that one out, but Power BI is throwing circular reference errors in a way that I wouldn't get in Excel.

Thanks in advance!

A | B | C | D | E |

Loan ID | Period | Beginning Debt | Amortization | Ending Debt |

X | 0 | $9,450 | $9,450 | |

X | 1 | $9,450 | ($53) | $9,398 |

X | 2 | $9,398 | ($53) | $9,345 |

X | 3 | $9,345 | ($53) | $9,293 |

X | 4 | $9,293 | ($53) | $9,240 |

X | 5 | $9,240 | ($53) | $9,188 |

X | 6 | $9,188 | ($53) | $9,135 |

X | 7 | $9,135 | ($53) | $9,083 |

X | 8 | $9,083 | ($53) | $9,030 |

X | 9 | $9,030 | ($9,030) | $0 |

Y | 0 | $7,500 | $7,500 | |

Y | 1 | $7,500 | ($45) | $7,455 |

Y | 2 | $7,455 | ($45) | $7,410 |

Y | 3 | $7,410 | ($45) | $7,365 |

Y | 4 | $7,365 | ($45) | $7,320 |

Y | 5 | $7,320 | ($45) | $7,275 |

Y | 6 | $7,275 | ($7,275) | $0 |

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

mrothschild

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-27-2019
01:54 PM

Wow, well that was way more difficult than it should be for such a simple financial concept, but given my search of the forums, this is a significant shortfall in BI.

The link here: https://www.dropbox.com/s/hryw5ezenojphzn/LoanAmortization.pbix?dl=0

has the PBIX file that does the job (though for those more sophisticated, it uses straight line debt amortization rather than PMT amortization).

The process, as I suspected, uses the workaround of calculating a running total cumulative amortization. That DAX is as follows:

Cumulative Amortization = CALCULATE( SUM(LoanAmortization[Amortization]), FILTER(ALL(LoanAmortization),LoanAmortization[Period]<=EARLIER(LoanAmortization[Period])), FILTER(ALL(LoanAmortization),LoanAmortization[Loan ID]=EARLIER(LoanAmortization[Loan ID]) ))

One also has to calculate the lagged cumulative amortization, which looks like above, but replaces " <= " with just " < ". as shown:

Cumulative Amortization (lagged) = CALCULATE( SUM(LoanAmortization[Amortization]), FILTER(ALL(LoanAmortization),LoanAmortization[Period]<EARLIER(LoanAmortization[Period])), FILTER(ALL(LoanAmortization),LoanAmortization[Loan ID]=EARLIER(LoanAmortization[Loan ID]) ))

Once those are calculated, and one has the Starting Loan Amount (which I created by using a related table, called "Starting Loan Amount"), then the Beginning Debt (calculated) is as follows:

Beginning Debt (calculated) = LoanAmortization[Starting Loan Amount] + LoanAmortization[Cumulative Amortization (lagged)]

and the Ending Debt (calculated) is as follows:

Ending Debt (calculated) = LoanAmortization[Starting Loan Amount] + LoanAmortization[Cumulative Amortization]

I'm still working through the last period amortization to avoid circular references, but I'm less concerend about that.

Hopefully this helps for all those who were looking for a solution.

Here's how the data looks for comparisons (there's some rounding that leads to imprecise comparisons as I moved from Excel to PBIX and back again, but the process works):

Loan ID | Period | Starting Loan Amount | Beginning Debt | Amortization | Ending Debt | Cumulative Amortization | Cumulative Amortization (lagged) | Beginning Debt (calculated) | Ending Debt (calculated) |

X | 0 | $9,450 | $9,450 | $9,450 | $9,450 | $9,450 | |||

X | 1 | $9,450 | $9,450 | ($53) | $9,397 | ($53) | $9,450 | $9,397 | |

X | 2 | $9,450 | $9,398 | ($53) | $9,345 | ($106) | ($53) | $9,397 | $9,344 |

X | 3 | $9,450 | $9,345 | ($53) | $9,292 | ($159) | ($106) | $9,344 | $9,291 |

X | 4 | $9,450 | $9,293 | ($53) | $9,240 | ($212) | ($159) | $9,291 | $9,238 |

X | 5 | $9,450 | $9,240 | ($53) | $9,187 | ($265) | ($212) | $9,238 | $9,185 |

X | 6 | $9,450 | $9,188 | ($53) | $9,135 | ($318) | ($265) | $9,185 | $9,132 |

X | 7 | $9,450 | $9,135 | ($53) | $9,082 | ($371) | ($318) | $9,132 | $9,079 |

X | 8 | $9,450 | $9,083 | ($53) | $9,030 | ($424) | ($371) | $9,079 | $9,026 |

X | 9 | $9,450 | $9,030 | ($9,030) | $0 | ($9,454) | ($424) | $9,026 | ($4) |

Y | 0 | $7,500 | $7,500 | $7,500 | $7,500 | $7,500 | |||

Y | 1 | $7,500 | $7,500 | ($45) | $7,455 | ($45) | $7,500 | $7,455 | |

Y | 2 | $7,500 | $7,455 | ($45) | $7,410 | ($90) | ($45) | $7,455 | $7,410 |

Y | 3 | $7,500 | $7,410 | ($45) | $7,365 | ($135) | ($90) | $7,410 | $7,365 |

Y | 4 | $7,500 | $7,365 | ($45) | $7,320 | ($180) | ($135) | $7,365 | $7,320 |

Y | 5 | $7,500 | $7,320 | ($45) | $7,275 | ($225) | ($180) | $7,320 | $7,275 |

Y | 6 | $7,500 | $7,275 | ($7,275) | $0 | ($7,500) | ($225) | $7,275 | $0 |

3 REPLIES 3

v-shex-msft

Community Support Team

Re: loan amortization?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-27-2019
01:03 AM

Hi @mrothschild ,

Power bi not allow you to do recursive calculation or circular reference between two fields.

Can you please share some sample data for test?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng

If this post**helps**, then please consider *Accept it as the solution* to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |

If this post

For learning resources/Release notes, please visit: | |

mrothschild

Member

Re: loan amortization?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-27-2019
05:12 AM

Here's a link to a PBIX with the same data posted in my original post. https://www.dropbox.com/s/hryw5ezenojphzn/LoanAmortization.pbix?dl=0

There is another way I think could work around to the right answer, which is to have a column that shows the cumulative amortization for a given period and loan id, such that beginning debt at period (t) = beginning debt at period (0) - cumulative amortization at period (t-1)

cumulative period amortization sum of amortization from periods 0 --> t-1

or as [period] * [amortization]

mrothschild

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-27-2019
01:54 PM

Wow, well that was way more difficult than it should be for such a simple financial concept, but given my search of the forums, this is a significant shortfall in BI.

The link here: https://www.dropbox.com/s/hryw5ezenojphzn/LoanAmortization.pbix?dl=0

has the PBIX file that does the job (though for those more sophisticated, it uses straight line debt amortization rather than PMT amortization).

The process, as I suspected, uses the workaround of calculating a running total cumulative amortization. That DAX is as follows:

Cumulative Amortization = CALCULATE( SUM(LoanAmortization[Amortization]), FILTER(ALL(LoanAmortization),LoanAmortization[Period]<=EARLIER(LoanAmortization[Period])), FILTER(ALL(LoanAmortization),LoanAmortization[Loan ID]=EARLIER(LoanAmortization[Loan ID]) ))

One also has to calculate the lagged cumulative amortization, which looks like above, but replaces " <= " with just " < ". as shown:

Cumulative Amortization (lagged) = CALCULATE( SUM(LoanAmortization[Amortization]), FILTER(ALL(LoanAmortization),LoanAmortization[Period]<EARLIER(LoanAmortization[Period])), FILTER(ALL(LoanAmortization),LoanAmortization[Loan ID]=EARLIER(LoanAmortization[Loan ID]) ))

Once those are calculated, and one has the Starting Loan Amount (which I created by using a related table, called "Starting Loan Amount"), then the Beginning Debt (calculated) is as follows:

Beginning Debt (calculated) = LoanAmortization[Starting Loan Amount] + LoanAmortization[Cumulative Amortization (lagged)]

and the Ending Debt (calculated) is as follows:

Ending Debt (calculated) = LoanAmortization[Starting Loan Amount] + LoanAmortization[Cumulative Amortization]

I'm still working through the last period amortization to avoid circular references, but I'm less concerend about that.

Hopefully this helps for all those who were looking for a solution.

Here's how the data looks for comparisons (there's some rounding that leads to imprecise comparisons as I moved from Excel to PBIX and back again, but the process works):

Loan ID | Period | Starting Loan Amount | Beginning Debt | Amortization | Ending Debt | Cumulative Amortization | Cumulative Amortization (lagged) | Beginning Debt (calculated) | Ending Debt (calculated) |

X | 0 | $9,450 | $9,450 | $9,450 | $9,450 | $9,450 | |||

X | 1 | $9,450 | $9,450 | ($53) | $9,397 | ($53) | $9,450 | $9,397 | |

X | 2 | $9,450 | $9,398 | ($53) | $9,345 | ($106) | ($53) | $9,397 | $9,344 |

X | 3 | $9,450 | $9,345 | ($53) | $9,292 | ($159) | ($106) | $9,344 | $9,291 |

X | 4 | $9,450 | $9,293 | ($53) | $9,240 | ($212) | ($159) | $9,291 | $9,238 |

X | 5 | $9,450 | $9,240 | ($53) | $9,187 | ($265) | ($212) | $9,238 | $9,185 |

X | 6 | $9,450 | $9,188 | ($53) | $9,135 | ($318) | ($265) | $9,185 | $9,132 |

X | 7 | $9,450 | $9,135 | ($53) | $9,082 | ($371) | ($318) | $9,132 | $9,079 |

X | 8 | $9,450 | $9,083 | ($53) | $9,030 | ($424) | ($371) | $9,079 | $9,026 |

X | 9 | $9,450 | $9,030 | ($9,030) | $0 | ($9,454) | ($424) | $9,026 | ($4) |

Y | 0 | $7,500 | $7,500 | $7,500 | $7,500 | $7,500 | |||

Y | 1 | $7,500 | $7,500 | ($45) | $7,455 | ($45) | $7,500 | $7,455 | |

Y | 2 | $7,500 | $7,455 | ($45) | $7,410 | ($90) | ($45) | $7,455 | $7,410 |

Y | 3 | $7,500 | $7,410 | ($45) | $7,365 | ($135) | ($90) | $7,410 | $7,365 |

Y | 4 | $7,500 | $7,365 | ($45) | $7,320 | ($180) | ($135) | $7,365 | $7,320 |

Y | 5 | $7,500 | $7,320 | ($45) | $7,275 | ($225) | ($180) | $7,320 | $7,275 |

Y | 6 | $7,500 | $7,275 | ($7,275) | $0 | ($7,500) | ($225) | $7,275 | $0 |