Double Recursive Calc..Is this possible?

[Anonymous]

Hi there,

Thanks to all for the amazing help.  I am trying to do a double recursive calculation where i want to apply two sets of factors against the same variable calculation.  Here is what it looks like in PowerBi and Excel.

The calculation should be as follows: Principal * (1- Factor) = Closing Bal After Amort, the tricky part is here, i want to calculate TWO measures against the Amort Amount, Factor2Calc = (1-Factor2)*Closing Bal After Amort and Factor3Calc=(1-Factor3)*Closing Bal After Amort. Then taking the total of those two from the Closing Bal After Amort.

So how it works in excel is right, 1,000,000*90% = 900,000 then that 900,000*5% = 45,000 and that same 900,000*13% = 112,500.  The closing balance then being 742,500.  I can get power bi to tie for the first row, but for the second row onwards PowerBi just doesnt match.

Here is the measure formula for Opening balance measure:

Opening Balance =

VAR MaxTime =

MAX ( 'Time'[Time Index] )

VAR OpeningBalance =

IF (

MaxTime = 1,

[Principal Sum],

CALCULATE (

[Closing Balance2],

ALL ( 'Time' ),

'Time'[Time Index] = MaxTime - 1

)

)

RETURN

OpeningBalance

Then to get the amortized balance:

Amort Amount =

VAR MaxTime =

MAX ( 'Time'[Time Index] )

RETURN

SUMX (

'ID',

VAR PrincipalSum = [Principal Sum]

VAR ClosingBalance2 = [Closing Balance2]

VAR ClosingBalance =

IF (

MaxTime = 1,

[Principal Sum]*( CALCULATE(sum(AmortizationPercentages[Factor]),

'Time'[Time Index] = MaxTime,

ALL ( 'Time' )

)),

( CALCULATE(sum(AmortizationPercentages[Factor]),

'Time'[Time Index] = MaxTime,

ALL ( 'Time' )

))

*((CALCULATE (

[Closing Balance2],

ALL ( 'Time' ),

'Time'[Time Index] = MaxTime - 1)

))

)

RETURN

ClosingBalance)

Then the calculations of the factor amounts (just showing one since the other is exactly the same except the factor that is referenced).

Factor2Calc =

VAR MaxTime =

MAX ( 'Time'[Time Index] )

RETURN

SUMX (

'ID',

VAR PrincipalSum = [Closing Balance]

VAR ClosingBalance =

PrincipalSum *

CALCULATE (

PRODUCTX (

Factor2,

(Factor2[Factor2] )

),

'Time'[Time Index] <= MaxTime,

ALL ( 'Time' )

)

RETURN

ClosingBalance

)

Any help here?  i am sure you guys have done multiple recursives before?  @OwenAuger you were my savior on this last time, any insight?

Thanks everyone!!

GZ

[v-chuncz-msft]

@Anonymous 

You may check if the following post helps.

https://community.powerbi.com/t5/Desktop/Calculate-with-previous-row-values-row-by-row/m-p/438597#M202375

[Anonymous · Accepted solution]

HI there, just to update everyone, the easiest solution to this is simply creating this at the column level rather than using a measure.  By building the columns i was able to have multiple columns based upon the result of another.  I could never figure out how to do it via measure, if anyone can do let me know.  

I just wanted to share my solution in case it helps someone else.