Calc Column Formula to increase a base rate by X percent annually

[Anonymous]

Hi,

I would like to create a calculated column that returns the Labor rate increased by 3% annually.

- If Date is in current year, return base rate

- If Date is in the future, return a value that is .03 percent more than what the rate would have been for the previous year

Below is a very simplified version of desired result, "Labor Rate" calculated column.

At first I thought I could do a DATEDIFF of future year - current year * .03 but that is not correct. It needs to be 3% more than whatever the previous year would have been.

TIA!

[AlexisOlson · Accepted solution]

This is simple periodic compounding where n=1 so that you have A = P(1 + r)^t where P is the principal (your Base Rate), r is the interest rate (your Annual % Increase) and t is the number of years beyond Year 1.

VAR Base = 40.5
VAR Rate = 0.03
VAR Years = DATEDIFF ( MIN ( Table1[Date] ), Table1[Date], YEAR )
RETURN
   Base * ( 1 + Rate ) ^ Years