Help with building a calculated table

[Saes]

Hello all, I'm hoping someone can help with this issue I've been struggling with. I'm trying to build a table in PowerBI desktop that shows inflation figures and then a calculated column, which shows a value based on the annual inflation figure - see table below. 

I've built the first 3 columns with no problem, but struggling with the Earnings Value column.

The starting figure is £37.45 and that is fixed, but the other values from row 2 onwards have the inflation figure applied to it. I've tried several formulas, but cannot work out how to calculate the year-on-year increase to replicate the Earnings Value column.

Can anyone help?

September Inflation Year	Inflation Figure	Financial Year Start	Earnings Value
1998	-	01/04/1999	£37.45
1999	2.09%	01/04/2000	£38.23
2000	4.30%	01/04/2001	£39.88
2001	2.20%	01/04/2002	£40.75
2002	2.20%	01/04/2003	£41.65
2003	3.30%	01/04/2004	£43.03
2004	3.60%	01/04/2005	£44.57
2005	3.20%	01/04/2006	£46.00
2006	4.10%	01/04/2007	£47.89
2007	4.40%	01/04/2008	£49.99
2008	5.50%	01/04/2009	£52.74
2009	-0.90%	01/04/2010	£52.27
2010	5.10%	01/04/2011	£54.93
2011	6.10%	01/04/2012	£58.28
2012	3.10%	01/04/2013	£60.09
2013	3.70%	01/04/2014	£62.32
2014	2.20%	01/04/2015	£63.69
2015	-1.00%	01/04/2016	£63.05
2016	-1.00%	01/04/2017	£62.42
2017	-1.00%	01/04/2018	£61.79
2018	-1.00%	01/04/2019	£61.18
2019	2.70%	01/04/2020	£62.83
2020	2.50%	01/04/2021	£64.40

[Tahreem24]

@Saes Try this Measure:

Earning measure = CALCULATE(((SUM(TableB[Earning Value])*SUM(TableB[Inflation Figure])))+SUM(TableB[Earning Value]),FILTER(ALL(TableB),TableB[September Inflation Year]<=Max(TableB[September Inflation Year])))

[Saes]

Thanks @Tahreem24 , but I require a calculated column, as it will be referenced by other data sets in the work flow.

[Tahreem24]

@Saes  For calculated column you need to replace MAX with Earlier and remove ALL.

Earning Column = CALCULATE(((SUM(TableB[Earning Value])*SUM(TableB[Inflation Figure])))+SUm(TableB[Earning Value]),FILTER((TableB),TableB[September Inflation Year]<=EARLIER(TableB[September Inflation Year])))

[Saes]

Thanks @Tahreem24 , apologies, I think there may be some confusion. It's the Earnings Value column that I'm trying to replicate, rather than creating another column.

[Tahreem24]

@Saes Yeah, I created Earning Value column and put only single value of 37.45 for first year manually. Then created that DAX on top of it. Attaching screen shot for your reference.

[Saes]

Thanks @Tahreem24 for clarifying. Is the calculated column reliant on having the Earning Value column already in place? The column I require is to calculate the Earning Value rather than duplicating an existing column. 

i.e. I know the dates, inflation figures and that the 1998 figure is $37.45 only. I need to be able to calculate the remaining figures based on this. Apologies if I've misunderstood or my post was not clear.

[Tahreem24 · Accepted solution]

@Saes Try this:

Column =

VAR res_ = CALCULATE(((37.45*SUM(TableB[Inflation Figure])))+37.45,FILTER((TableB),TableB[September Inflation Year]<=EARLIER(TableB[September Inflation Year])))

RETURN IF(TableB[September Inflation Year]=1998,37.45,res_)

[Saes]

Thanks @Tahreem24  for your help!