Calculate Target Rate Based on Previous Year\Record

[Anonymous]

Hello

I have a table with a Year field and Avg Amount field. I need to create a field that will Calculate A Target Amount based on the previous Year Avg.  For example, my data starts with Year 2012 and My Avg(Amount) is 40, Since there no previous record there is no target rate.  However for the next Year -2013, my Avg(Amount) is 45, my target rate is to be with in 90% of 2012 Amount average, which would be 36 (Or not to diminish more than 10% following year).

The table below hopefully puts thing into perspective.

Year	avg(Amount)	Target
2012	40	Null (since its first Year) or could be 40
2013	45	36  (40  * 0.9)
2014	50	40.5  (45 * 0.9)
2015	45	45 (50 * 0.9)
2016	37	40.5  (45 * 0.9)

Please help.

Thank you,

[sunny_talwar · Accepted solution]

May be like this

Above(Avg(Amount)) * 0.90

[Anonymous]

I also found that

above(Column(1))

also works.

Thank you