Rolling 12 months with 'MMM YYYY'

[TomBond77]

Hi experts

I need this formula, set expression, working dynamically:

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])

I have defined this formula to be dynamically, but the result is '0' for amount. 

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

The resolution is fine. Where is the error?

,

[ogster1974]

assuming thats part of your master calendar take a step back and use your date not a period label.

sum({<[Date]={">$(=AddMonths(Max(MonthStart([Date])),-12))"}>} Amount)

[vinieme12]

[Year/Month]  is a text field, you cannot use operators like >,< on text values

Best to  use the Datefield in your dataset instead

As below

= sum({<Datefield={">=$(=YearStart(Max(Datefield)))<$(=Date(Max(Datefield)))"}>}Amount)

Or if you don't have a date field, create one while loading the data as below

MonthStart(Date#([Year/Month],'MMM YYYY')) as Datefield

Or Try below

= sum({<"=MonthStart(Date#([Year/Month],'MMM YYYY'))"={">=$(=YearStart(Max(Date#([Year/Month],'MMM YYYY'))))<$(=MonthStart(Max(Date#([Year/Month],'MMM YYYY'))))"}>}Amount)

[ogster1974]

Plus your example with single quotes means text match what's within so you might as well have been asking for [Year/Month]={'Elephant'} the result would be the same 0.