previous month / year value

[lee_]

Hi,

I have a master calendar which has an Auto number ID field [_monthserial] around the months  to give an increasing number as the months progress.

What i'm trying to display in a pivot table is the current months value side by side with last year value using the following 

=sum(  {< [_Monthserial] = {$(=Max([_Monthserial])-13)} >} Month_Actual)

the issue being is the calendar goes beyond the current date by a few years, so 'max (monthserial) ' doesn't work. I've tried various versions but none work but essentially I'm trying to get the following 

{< [_Monthserial] = {([_Monthserial])-13} >}

any ideas please

[eronevil]

sum(  {< [_Monthserial] = {$(=year(today())-1)} >} Month_Actual)

Does that help?

[vinieme12]

You can base all calculation periods on your datefield instead

latest Month

=sum(  {< Datefield ={">=$(=Date(Monthstart(Max(Datefield))))<=$(=Date(Max(Datefield)))"} >} Month_Actual)

Last Year Same Month

=sum(  {< Datefield ={">=$(=Date(Addyears(Monthstart(Max(Datefield)),-1)))<=$(=Date(Addyears(Max(Datefield),-1)))"} >} Month_Actual)

Previous Month

=sum(  {< Datefield ={">=$(=Date(Monthstart(Max(Datefield),-1)))<=$(=Date(Monthend(Max(Datefield),-1)))"} >} Month_Actual)

last year Previous Month

=sum(  {< Datefield ={">=$(=Date(Addyears(Monthstart(Max(Datefield),-1),-1)))<=$(=Date(Addyears(Monthend(Max(Datefield),-1),-1)))"} >} Month_Actual)