In an attempt to get the previous month records I have tried the following expression:
If((Month([Site Date])=(Month(DateTimeNow()) - 1)) and (Year([Site Date])=Year(DateTimeNow())),1,0)
But this has a problem when DateTimeNow() is January. Since the year will be changing from 2016 to 2017, year condition does not satisfy and there will be some discrepancy in fetching the records.
I have the same problem with Previous Quarter records too.
It would be helpful if someone helps me in fixing the above code.
Thank you all beforehand!
Regards
Santosh
You are correct in that you need to check the year and the month, but you need to use DATEADD() to get the correct values. Using this method, you compare it to the Year and Month of the previous month, regardless of where it falls on the calendar. E.g DateAdd('month',-1,Date(2017,01,26)) will be 12/26/2016
If(Month([Site Date])=Month(DateAdd("month",-1,DateTimeNow())) and Year([Site Date])=Year(DateAdd("month",-1,DateTimeNow())),1,0)
You can test this by forcing the DateTimeNow() to a January date.
If(Month([Site Date])=Month(DateAdd("month",-1,Date(2017,01,26))) and Year([Site Date])=Year(DateAdd("month",-1,Date(2017,01,26))),1,0)