Last Full Month

[#1]

Hi,

I am trying to run a report for last full month regardless of the date

For example if I run the report now I would like to retrieve May's data. It also needs to be dynamic.

Any help would be much appreciated

[swuehl]

I assume you would consider Today =  Jun 30 to indicate a full month?

Try

Sum({<[Bill Date]={">= $(=monthstart(Monthend(today()+1,-1))) <=$(=monthend(today()+1,-1))"} >} [Bill Value])

Take care that the format of your Bill Date field values match the format of your dollar sign expanded values.

[Anonymous]

try to put num around Expression

Sum({<[Bill Date]={">= $(=num(monthstart(addmonths(today(),-1))) <=$(=num(monthend(addmonths(today(),-1))))"} >} [Bill Value]) 

[#4]

this shows nothing again

[varshavig12 · Accepted solution]

Try this:

sum({<[Bill Date]={"<$(=MonthStart(today())) >=$(=MonthStart(addmonths(today(),-1))) "}, Month=  >} [Bill Value] )

[varshavig12]

Try this for DD/MM/YYY format:

sum({<[Bill Date]={"<$(=date(MonthStart(today()),'DD/MM/YYYY') >=$(=date(MonthStart(addmonths(today(),-1)),'DD/MM/YYYY') "}, Month= , Year= >} [Bill Value] )

This should give you:

sum({  [Bill Value]={  < '06/01/2016'  and >= '05/01/2016'  }  } [Bill Value] )

Note: instead of today() you can use max(Date_Field)

[#7]

Fantastic it works, thank you so much.

if I wanted the formula to return Aprils data instead of May. What would I need to change?