Sum using AddYears Function for Date Formatted MMDDYYYY

[#1]

Hello,

I am having difficulties with the AddYears function just when I though I had figured it out and was working.  I loaded my data setting the date format as follows:

SET DateFormat='MM/DD/YYYY';

I am trying to sum a value for a specific column based on the value for 1 year prior to the max year in the table using the following expression:

=Sum({$<[POS Date]={"$(=Num(AddYears(Date(Max([POS Date],'MMDDYYYY')),-1)))"}>}[POS Adjusted Fair Value])

At one point this was functioning, however when I reloaded my updated data something changed.  Any help would be appreciated.

[sunny_talwar]

Try this (Assuming your [POS Date] is a number formatted field

=Sum({$<[POS Date]={"$(=Num(AddYears(Date(Max([POS Date])),-1)))"}>}[POS Adjusted Fair Value])

Best,

Sunny

[#3]

Sunny,

It is actually formatted as a Date 'MMDDYYYY".

[#4 · Accepted solution]

I updated your expression as follows and it worked:

=Sum({$<[POS Date]={"$(=Date(AddYears(Date(Max([POS Date])),-1)))"}>}[POS Adjusted Fair Value])

[sunny_talwar]

I am glad it worked for you Jeff.

Best,

Sunny