- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Engagement
- T-Shirt Design Challenge 2023
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

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

Showing results for

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: 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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

loan amortization?

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- 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 |

5 REPLIES 5

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- 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

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

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- 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]

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- 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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-08-2020
04:01 AM

@mrothschild

The dropbox link for the PBIX file says that the file has been deleted. Can you upload the PBIX again?

Announcements

Check out the November 2023 Power BI update to learn about new features.

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Featured Topics