AddYears - how to?

[#1]

how do I modify the code below to be able to 1 year less than the SELECTED year?　 I tried adding the ADDYEARS and a -1 to the code, but couldn’t get it to work correctly.　

=Num(Sum({<[Account Description] = {'Total COGS'}, MonthYear = {"$(='>' & Date(AddMonths(QuarterStart(AddMonths(Max(MonthYear), 1))-1, -12)) & '<=' & Date(QuarterStart(AddMonths(Max(MonthYear), 1))-1))"}, Year, Month, Quarter>} Actual),'#,##0')

Thank you for any assistance you can offer!!!!

Kelly

stalwar1‌

[nagarjuna_kotha]

what is ur year format ?

-nagarjun

[sunny_talwar · Accepted solution]

May be this:

=Num(Sum({<[Account Description] = {'Total COGS'}, MonthYear = {"$(='>' & Date(AddMonths(QuarterStart(AddMonths(Max(MonthYear), 1))-1, -24)) & '<=' & Date(AddMonths(QuarterStart(AddMonths(Max(MonthYear), 1))-1, -12))"}, Year, Month, Quarter>} Actual),'#,##0')

[#4]

2008

[#5]

I added an additional

)

after the last -12 and it worked like a charm! 

Thank you - Sunny T!

Kelly

[sunny_talwar]

Right, I forgot to add that

[sunny_talwar]

Wow, you just pushed my past 100,000

[#8]

YAY Sunny T!  Congrats! 

[sunny_talwar]

Thanks Kelly