cancel
Showing results for
Did you mean:
Regular Visitor

## Need help with circular reference calculation using varying percentages

Need experts help here as have been banging my head against this for awhile.  Tried using @OwenAuger s circular reference calculation but was unable to as my issue has percentages.  Here is the issue i need to do a simple amortization of a balance.

i have one table containing the amortization in percetages with a time index. See below:

I have another table with the balance:

All i want is to apply -.1*1,000,000=900,000 for the first time period then for the second time period start with 900,000 and multiply that by -0.2.

Here is what i can come up with, as you can see it's not working right.

The Amort measure is as below:

AmortCalc =
(-[Initial Balnce Measure2]*[Market Factor])

I cant change it to reference the closing balance as it creates a circular reference.

Closing Balance calculation is as below:
Closing Balance =
[Initial Balnce Measure2]+[Amort Cumulative]

I realize there must be a simple answer to this.  Appreciate your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Need help with circular reference calculation using varying percentages

Hi @GeraldZ

Attached is an example of how I would handle this.

The main requirement is to calculate the Closing Balance first by taking a cumulative product of (1-Factor) multiplied by the Initial Principal.

1. I set up data model like this:

2. Created these measures:
```Principal Sum =
SUM ( Principal[Initial Principal] )

Closing Balance =
VAR MaxTime =
MAX ( 'Time'[Time Index] )
RETURN
SUMX (
'ID',
VAR PrincipalSum = [Principal Sum]
VAR ClosingBalance =
PrincipalSum *
CALCULATE (
PRODUCTX (
AmortizationPercentages,
(1 - AmortizationPercentages[Factor] )
),
'Time'[Time Index] <= MaxTime,
ALL ( 'Time' )
)
RETURN
ClosingBalance
)

Opening Balance =
VAR MaxTime =
MAX ( 'Time'[Time Index] )
VAR OpeningBalance =
IF (
MaxTime = 1,
[Principal Sum],
CALCULATE (
[Closing Balance],
ALL ( 'Time' ),
'Time'[Time Index] = MaxTime - 1
)
)
RETURN
OpeningBalance

AmortCalc =
[Closing Balance] - [Opening Balance]```
3. Then you can visualize similar to what you posted:

The above measures should aggregate correctly across multiple IDs but I haven't tested that.

Hopefully that's some help

Regards,

Owen

Proud to be a Datanaut!

2 REPLIES 2
Super Contributor

## Re: Need help with circular reference calculation using varying percentages

Hi @GeraldZ

Attached is an example of how I would handle this.

The main requirement is to calculate the Closing Balance first by taking a cumulative product of (1-Factor) multiplied by the Initial Principal.

1. I set up data model like this:

2. Created these measures:
```Principal Sum =
SUM ( Principal[Initial Principal] )

Closing Balance =
VAR MaxTime =
MAX ( 'Time'[Time Index] )
RETURN
SUMX (
'ID',
VAR PrincipalSum = [Principal Sum]
VAR ClosingBalance =
PrincipalSum *
CALCULATE (
PRODUCTX (
AmortizationPercentages,
(1 - AmortizationPercentages[Factor] )
),
'Time'[Time Index] <= MaxTime,
ALL ( 'Time' )
)
RETURN
ClosingBalance
)

Opening Balance =
VAR MaxTime =
MAX ( 'Time'[Time Index] )
VAR OpeningBalance =
IF (
MaxTime = 1,
[Principal Sum],
CALCULATE (
[Closing Balance],
ALL ( 'Time' ),
'Time'[Time Index] = MaxTime - 1
)
)
RETURN
OpeningBalance

AmortCalc =
[Closing Balance] - [Opening Balance]```
3. Then you can visualize similar to what you posted:

The above measures should aggregate correctly across multiple IDs but I haven't tested that.

Hopefully that's some help

Regards,

Owen

Proud to be a Datanaut!

Regular Visitor

## Re: Need help with circular reference calculation using varying percentages

Wow amazing!  Let me try this out today and work it out.  Am sure it will work.  Thanks for your help @OwenAuger !!

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 308 members 3,429 guests
Recent signins: