rolling 12 months calculation

[TomBond77]

Hi experts

My formula to calculate the last rolling 12 months is not working: Sum({$<[Year/Month]={'>=$(=Monthstart(Addmonths(Max([Year/Month]),-11)))<=$(=Max([Year/Month]))'} [Amount]>})

It seems to have a problem with the second Year/Month. The Month -11 is resolved correctly with 01.01.2022.

any ideas?

[MayilVahanan]

Hi

Try with Date field

Sum({$<[Date]={">=$(=Monthstart(Addmonths(Max(Date),-11)))<=$(=Date(Max(Date)))"}>} [Amount])

[TomBond77]

Thank you

I tried it, it is showing 0 as result, therefore there is something wrong. With the inactive formula (set expression), see below, it is working.   Any ideas?

Sum({<[Year/Month]={'>=$(=Monthstart(Addmonths(Max([Year/Month]),-11)))<=$(=Date(Max([Year/Month])))'}>}
[Amount])

/*
Sum({<[Year/Month]={'Feb 2022','Mar 2022','Apr 2022','May 2022','Jun 2022','Jul 2022','Aug 2022','Sep 2022','Oct 2022','Nov 2022','Dec 2022','Jan 2023'}>} [Amount])
,