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
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.
try to put num around Expression
Sum({<[Bill Date]={">= $(=num(monthstart(addmonths(today(),-1))) <=$(=num(monthend(addmonths(today(),-1))))"} >} [Bill Value])
this shows nothing again
Try this:
sum({<[Bill Date]={"<$(=MonthStart(today())) >=$(=MonthStart(addmonths(today(),-1))) "}, Month= >} [Bill Value] )
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)
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?