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

[ciaran_mcgowan]

Hi Dave,

Can you post a copy of your report as it exists now. Usually you would use AddMonths function to achieve this but if I can see what you're trying to accomplish, I might be able to help more.

AddMonths(Max(YourDate),-1)

[Anonymous]

if you Need to run your Report with last months data you have two Options depending on your datamodel

1) you have field which contains full date Format (day, month, year)

  then you Need to run your Report with

  (yourdatefield >= monthstart(addmonths(today(),-1)) and (yourdatefield <= monthend(addmonths(today,-1))

2) if you have a month field and year field

    (youryearfield = year(addmonths(today(),-1)) and   (youmonthfield = month(addmonths(today(),-1))

[Anonymous]

Hi Dave,

Make a variable with this code:

     If(YourDate = MonthEnd(YourDate),YourMonth,Month(Addmonths(YourDate),-1))

With this code you will test if current month is complete, if it is true you will get current month, else, you will get last complete month.

Select your report with this var.

Regards!!

[juleshartley]

you might need a 'floor' in this to be sure no time element causing issues...

if(YourDate=Floor(MonthEnd(YourDate)), Floor(MonthEnd(YourDate)), Floor(MonthEnd(AddMonths(YourDate,-1))))

[Anonymous]

If the field is date format, and not is TimeStamp format, you don't need to use Floor function.

Regards.

[#7]

this errors after the and ay ideas?

[Anonymous]

can you post sample?

[susovan]

Hi Dave,

Try this expression,

sum({<Date={">=$(vMonthStart)<=$(vMonthEnd)"},Month=,Quarter=,FullYear=>} Amount)

Here

vMonthStart=Date(MonthStart(Max(@_Date)),'DD/MM/YYYY')

vMonthEnd=Date(Max(@_Date),'DD/MM/YYYY')

[#10]

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

I am trying to sum bill value by bill date