Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Susovan
Thanks for your reply. I don't want to add variables as the report needs to be dynamic
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])
So, you can try this
sum({<Date={">=$(=Date(MonthStart(Max(@_Date)),'DD/MM/YYYY'))<=$(=Date(Max(@_Date),'DD/MM/YYYY'))"},Month=,Quarter=,FullYear=>} Amount)
thought it was the syntax. This display data going back to March, what do I need to do to change it to May only
try this (to get only may data)
Sum({<[Bill Date]={">= 01.05.2016<=31.05.2016"} >} [Bill Value])
is there a more dynamic way to do it?
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
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
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?
the expression shows 01/05/2016-42544......not sure why as the date shows correctly in the table