Sum of two values + another

[jnw221]

I'm trying to get the cumulative total of the original loan balance plus interest. In excel, the output would look like this:

Loan Balance	Rate	Days	Interest Due	Cumulative Interest	Cumulative Total
$353,500	11.00%	18	$1,944.25	$1,944.25	$355,444.25
$353,500	11.00%	30	$3,240.42	$5,184.67	$360,628.92
$353,500	11.00%	31	$3,348.43	$8,533.10	$369,162.02
$353,500	11.00%	31	$3,348.43	$11,881.53	$381,043.55
$353,500	11.00%	28	$3,024.39	$14,905.92	$395,949.47
$353,500	11.00%	31	$3,348.43	$18,254.35	$414,203.82
$353,500	11.00%	30	$3,240.42	$21,494.77	$435,698.59
$353,500	11.00%	31	$3,348.43	$24,843.20	$460,541.79
$353,500	11.00%	30	$3,240.42	$28,083.62	$488,625.41
$353,500	11.00%	31	$3,348.43	$31,432.05	$520,057.46
$353,500	11.00%	31	$3,348.43	$34,780.48	$554,837.94
$353,500	11.00%	30	$3,240.42	$38,020.90	$592,858.84
$353,500	11.00%	31	$3,348.43	$41,369.33	$634,228.17

However, in Power BI, I'm unable to get the "Cumulative Total" column. My results just give me "Loan Balance" + "Interest Due" or "Loan Balance" + "Cumulative Interest" and what I need is the "Total Cumulative Total" + "Cumulative Interest".

Any insight is much appreciated. 

[jnw221]

Thank you for the response. I do have a date in the data, a date table and an index, if needed.

The result you show isn't the output I'm looking for. I'm trying to get to what's shown in the "cumulative total" column. 

I think the first step is getting "loan balance" + "cumulative interest" = "cumulative total". 

Which, given the data would be 353,500 + 1,944.25 = 355,444.25. This equation is not a problem since it's a straight line on the table. The issue is keeping the total (355,444.25) and applying it to a different equation going forward. 

Meaning, the next, and ongoing equation would be "previous cumulative total" + "current cumulative interest" = "current cumulative total" which is:

355,444.25 + 5,184.67 = 360,628.92

360,628.92 + 8,533.10 = 369,162.02

369,162.02 + 11,881.53 = 381,043.55

And so on.....

[Anonymous]

Hi @jnw221 ,

Please have a try.

Measure =
VAR _next =
    CALCULATE (
        MAX ( 'Table'[Cumulative Interest] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index]
                = SELECTEDVALUE ( 'Table'[Index] ) + 1
        )
    )
RETURN
    MAX ( 'Table'[Cumulative Total] ) + _next

If I have misunderstood your meaning, please provide more details with the desired output and pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jnw221]

Ok, yes.  This is the correct "ongoing" calc--thank you! However, it's not giving me the primary calc of 353,500 + 1,944.25 = 355,444.25; it's starting with 355,444.25 + 5,184.67 = 360,628.92.

Any thoughts on how to get that first piece of "loan balance" + "cumulative interest" = "cumulative total"? 

Gettin' close. 🙂

[Anonymous · Accepted solution]

Hi @jnw221 ,

Please have a try.

Create 2 columns.

cumulative total column = 
var _minindex=MINX(('Table'),'Table'[Index])
var _re=CALCULATE(SUM('Table'[Cumulative Interest]),FILTER('Table','Table'[Index]<>_minindex&&'Table'[Index]<=EARLIER('Table'[Index])))
var _1re=IF('Table'[Index]=_minindex,'Table'[Loan Balance]+'Table'[Cumulative Interest],BLANK())
var _cumu=MAXX('Table',_1re)
return _cumu

Column = var _1= MAXX('Table','Table'[cumulative total column])
var _minindex=MINX(('Table'),'Table'[Index])
var _2=CALCULATE(SUM('Table'[Cumulative Interest]),FILTER('Table','Table'[Index]<>_minindex&&'Table'[Index]<=EARLIER('Table'[Index])))
return
IF('Table'[Index]=_minindex,'Table'[cumulative total column],_1+_2)

Then create a measure or a column.

Measure = var _next=CALCULATE(MAX('Table'[Column]),FILTER(ALL('Table'),'Table'[Index]=SELECTEDVALUE('Table'[Index])+1))
return 
_next

Column 2 = var _next=CALCULATE(MAX('Table'[Column]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])+1))
return 
_next

If I have misunderstood your meaning, please provide more details with the desired output and pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jnw221]

Amazing stuff--that worked. Thank you so much. I really appreciate the time and attention you gave this. 

Best! 

[Anonymous]

Hi @jnw221 ,

 I have created a simple sample, please refer to it to see if it helps you.

There is no date in the table.

Add an index in the Power Query.

Then create a measure.

Measure =
VAR _loanbalance =
    CALCULATE (
        SUM ( 'Table'[Loan Balance] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] ) )
    )
VAR _interestdue =
    CALCULATE (
        SUM ( 'Table'[Interest Due] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] ) )
    )
RETURN
    _interestdue + _loanbalance
        + MAX ( 'Table'[Cumulative Interest] )

If I have misunderstood your meaning, please provide more details with the desired output and pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jnw221]

Is there any information I can add to this? I see a lot views but no responses. 

Just talking through this with myself, the first calc would be 'loan balance'+'interest due' = 'cumulative total'.

However, after that first month, it's then [previous months 'cumulative total']+[current month 'cumulative interest'] = 'cumulative total' and then that's the formula going forward.

Now how to I get that in power bi?? 🙂