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

[#2]

Hi Susovan

Thanks for your reply. I don't want to add variables as the report needs to be dynamic

[Anonymous]

Syntax is wrong (not sure if my Syntax is correct - could not test it)

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

[susovan]

So, you can try this

sum({<Date={">=$(=Date(MonthStart(Max(@_Date)),'DD/MM/YYYY'))<=$(=Date(Max(@_Date),'DD/MM/YYYY'))"},Month=,Quarter=,FullYear=>} Amount)

[#5]

thought it was the syntax. This display data going back to March, what do I need to do to change it to May only

[Anonymous]

try this (to get only may data)

Sum({<[Bill Date]={">= 01.05.2016<=31.05.2016"} >} [Bill Value])

[#7]

is there a more dynamic way to do it?

[Anonymous]

can you specify more in Detail your requirements?

if you have the data in a variable for instance

Sum({<[Bill Date]={">= $(=monthstart(variablemonthdate))<=$(=monthend(variablemonthdate))"} >} [Bill Value])

or if you have two variable for start and end date

Sum({<[Bill Date]={">= $(=variablemonthstart)<=$(=variablemonthend)"} >} [Bill Value])

please check that variable and field have same dateformat

[#9]

Rduolf - I'm nearly I think. I've put the formula in as detailed above

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

Unfortuante;y I'm getting data from March to the end of May any idea why. I only want the data from the last full month

[Anonymous]

please try this Expression in a TextBox

=monthstart(addmonths(today(),-1)) & '-' & max([Bill Date])

does both dates are from same Format?

does first Expression Shows 1.05.2016?

[#11]

the expression shows 01/05/2016-42544......not sure why as the date shows correctly in the table