cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GeraldZ Regular Visitor
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:

Data2.PNG

I have another table with the balance:

 

Balance.PNG

 

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.

 

Amort Table.PNG

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
OwenAuger Super Contributor
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:image.png

     

  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:image.png

     

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

 

Hopefully that's some help Smiley Happy

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
OwenAuger Super Contributor
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:image.png

     

  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:image.png

     

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

 

Hopefully that's some help Smiley Happy

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




GeraldZ Regular Visitor
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 !!

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 308 members 3,429 guests
Please welcome our newest community members: