Need help with circular reference calculation using varying percentages

[Anonymous]

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!

[OwenAuger · Accepted solution]

Hi @Anonymous 

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

[Anonymous]

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