Get a value one year back based on the current value

[kpatelacmm]

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.

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

Thanks

[sunny_talwar]

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

[#3]

can you attach an example?

[maxgro]

Dateofservice and DATE are 2 dates?

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

[kpatelacmm]

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.

[kpatelacmm]

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.