Previous row for cumulative total

[Anonymous]

Hi all,

Been trying to solve the following issue which I've been given from Excel and translating it to PBI.

How do I calculate the Pension for each year ?

Pension Fund  

Year	Pension + 2.5%	Interest	Yearly Spend
2020	£500,000	£12,500	£15,000
2021	£497,500	£12,438	£15,500
2022	£494,438	£12,361	£16,750
2023	£490,048	£12,251	£18,000

Initial value $500,000 and each year I can spend £x amount (could be any value)

The following years incur interest at 2.5%, but ONLY on the previous year Pension value : (Previous Pension * 2.5%) - Previous Spend

note: I dont have a value for years 2021+ this is calculated from the formula above

2021 Pension value = (£500,000 * 1.025) - £15,000 = £497,500

2022 Pension value = (£497,500 * 1.025) - £15,500 = £494,438

etc

I've tried various methods but can't get this to work. The following doesn't work as it doesn't calculate the spend each year:
[Pension Fund] *

CALCULATE

(

PRODUCTX ( FILTER( ALL('Pension'),'Pension'[Year] +1 < actualyear) , 1.025 )

)

and adding -Spend at the end doesn't calculate correctly.

Any pointers would be greatly received.

[Anonymous]

Hi there.

I've got the solution but need to work out the details. I'll give you a hint about how to do it but if you wait a bit longer, I'll post the full solution.

Basically, there is a formula that gives you the pension for any particular year. If the initial capital is K, r is the interest rate and s(j) is the j-th spend, then the formula is this (and we're starting by enumerating the years from 0 up to n):

P = K(1+r)^n - sum over j from 0 to n-1: s(j)*(1+r)^(n-1-j)

if n > 0 and

P = K if n = 0.

It's just a question of translating this into DAX for each year j.

Best

Darek

[Anonymous · Accepted solution]

I don't know what happened to my solution... It's disappeared! I'm posting again 😞

Pension = 
var __startAmount = SELECTEDVALUE( 'Start Amounts'[Start Amount] )
var __interestRate = SELECTEDVALUE( 'Interest Rates'[RateValue] )
var __multiplier =  (1+__interestRate)
var __currentPensionYear = SELECTEDVALUE( 'Pension Years'[Year] )
var __allSelectedYears = ALLSELECTED( 'Pension Years'[Year] )
var __currentYearNumber = -- counting from 0
    CALCULATE(
        COUNTROWS( 'Pension Years' ),
        'Pension Years'[Year] < __currentPensionYear,
        __allSelectedYears
    ) + 0
var __pension =
    __startAmount * __multiplier^__currentYearNumber
    - sumx(
        CALCULATETABLE(
            'Pension Years',
            'Pension Years'[Year] < __currentPensionYear,
            __allSelectedYears
        ),
        var __iterationYear = 'Pension Years'[Year]
        var __yearlySpend = 'Pension Years'[Yearly Spend]
        var __iterationYearNumber =
            CALCULATE(
                COUNTROWS( 'Pension Years' ),
                'Pension Years'[Year] < __iterationYear,
                __allSelectedYears
            ) + 0
        return
            'Pension Years'[Yearly Spend]
                * __multiplier^(__currentYearNumber - 1 - __iterationYearNumber)
    )
return
    __pension

and the layout:

You'll have to adjust the code for the Total. I'm not sure what you want for Total. Maybe it should return the pension for the last year? Maybe it should be BLANK? I don't know as it's a business decision.

Secondly, even though my years are tagged from 0 up to n, this normally would be the years you want, 2000, 2001, 2002... It does not matter what it is. What matters is that they are distinct values and there is an order among them.

Best

Darek

[Anonymous]

Thank you, this is perfect. No wonder I couldn't figure this out!!

[Anonymous]

Here's the measure:

Pension = 
var __startAmount = SELECTEDVALUE( 'Start Amounts'[Start Amount] )
var __interestRate = SELECTEDVALUE( 'Interest Rates'[RateValue] )
var __multiplier =  (1+__interestRate)
var __currentPensionYear = SELECTEDVALUE( 'Pension Years'[Year] )
var __allSelectedYears = ALLSELECTED( 'Pension Years'[Year] )
var __currentYearNumber = -- counting from 0
    CALCULATE(
        COUNTROWS( 'Pension Years' ),
        'Pension Years'[Year] < __currentPensionYear,
        __allSelectedYears
    ) + 0
var __pension =
    __startAmount * __multiplier^__currentYearNumber
    - sumx(
        CALCULATETABLE(
            'Pension Years',
            'Pension Years'[Year] < __currentPensionYear,
            __allSelectedYears
        ),
        var __iterationYear = 'Pension Years'[Year]
        var __yearlySpend = 'Pension Years'[Yearly Spend]
        var __iterationYearNumber =
            CALCULATE(
                COUNTROWS( 'Pension Years' ),
                'Pension Years'[Year] < __iterationYear,
                __allSelectedYears
            ) + 0
        return
            'Pension Years'[Yearly Spend]
                * __multiplier^(__currentYearNumber - 1 - __iterationYearNumber)
    )
return
    __pension

which is the implementation of the formula given before. Once you have this, it's easy to obtain the interest for each year.

And here's the layout:

Best

Darek

[avanderschilden]

Hello,

How does your data model look like?

Which amounts from your example table are coming from the data model and which columns are calculated?

Regards,

Adrian

[Anonymous]

From my data model I have Year, Initial pension and Spend Amount

Year	Pension + 2.5%	Yearly Spend
2020	£500,000	£15,000
2021		£15,500
2022		£16,750
2023		£18,000

I've created a new table Pension with:

Pension = GENERATESERIES ( 2019, 2090, 1 ) for the Year column.

The Pension interest is a parameter value which can be changed.