Help with pulling data for 4 months including 1 month back.

[ginkoba]

I'm trying to figure out how to find data within a 4 month period. So when the report runs, it should pull data for the last month(1 month back) + 3 months. So if I run the report now, it should pull all June, July, August and September. Can someone please assist with this. I have this formula below that pulls all data from 1st of previous month to the last day of previous month. If I can get it to pull for 4 months, including 1 previous month back, that would be great.

{DocDate} in dateserial(year({?enddate}),month({?enddate})-1,1)
to
DateSerial(year({@EndDate}), month({@EndDate}), 1) -1

 

[briangriffin]

This should do the trick:

{DocDate} in [minimum(lastfullmonth) to date(dateadd("m",3,maximum(lastfullmonth)))]

Not sure why you have the parameter field - also, if the DocDate is a datetime field you'll have to do something like this:

({DocDate >= minimum(lastfullmonth)
and
{DocDate < date(dateadd("m",3,maximum(lastfullmonth))) + 1 )

Otherwise you'd be missing any records with a value after midnight on 9/30.

 

[ginkoba]

Thanks much Brian, the 2nd option worked for me as I had a datetime field.