5 Replies Latest reply: Mar 23, 2015 5:10 PM by Krishan Patel

# Get a value one year back based on the current value

Current date = 201503

I am trying to get the Bonus Amt and MM one year back based on the current date. I tried the formula below but its not working.

/

Thanks

• ###### Re: Get a value one year back based on the current value

Not entirely sure if the formula is correct or not, but the syntax is definitely something which I can help you fix:

=Sum({\$<Month= , YEAR= , Rolling12= , QUARTER= , DateofService= {'\$(=AddMonths(DATE,-12))'}>} Bonus_Amt)
/
=Sum({\$<Month= , YEAR= , Rolling12= , QUARTER= , DateofService={'\$(=AddMonths(DATE,-12))'}>} MM)

HTH

Best,

Sunny

• ###### Re: Get a value one year back based on the current value

can you attach an example?

• ###### Re: Get a value one year back based on the current value

Attached is an example.

Basically I have months going across. If you look at the PCP Bonus 201407 to Current is zero. What I want to do is be able to look back one year so for example for 201407 look back one year to 201406 and see the PMPM or 1.55 then use that 1.55 and multiply that by 2622(201407 MM). But it needs to look back one year for each of the column where the PCP Bonus is zero.

• ###### Re: Get a value one year back based on the current value

Dateofservice and DATE are 2 dates?

=Sum({\$<Month= , YEAR=, Rolling12=, QUARTER=, DATE=, DateofService={'\$(=date(addmonths(max(DATE),-12),'DD/MM/YYYY'))'}>} Bonus_Amt)

• ###### Re: Get a value one year back based on the current value

Both are the same date just different format.

DateofService is yyyymm format. DATE is in mm/dd/yyyy format so I was converting the date format.