Date range selection by the system date

[jeetamar11]

Hi, 

I am using this formula to choose all the days in the month Month to date.

DateTimeMonth([Case Created Date]) = DateTimeMonth(DateTimeNow()) AND DateTimeYear([Case Created Date]) = DateTimeYear(DateTimeNow())

But today(May 1st) when we are in new month but I still need to report data as per full April month. The querry is giving me no result as system date is new month. Can someone help me sp When we are on the forst of new month -it givers me data for whole last month.?

thank you in advance!

[rzdodson]

@jeetamar11 in lieu of the DateTimeMonth([Case Created Date]) = DateTimeMonth(DateTimeNow()) part, you can try the following in its place:

ToNumber(DateTimeFormat(DateTimeToday(),"%U"))-ToNumber(DateTimeFormat([Date],"%U"))<=4

What is happening here is that we are finding the week numbers for today and your date field, converting them in to numbers, and then testing whether they are less than or equal to the last four weeks. If so, then we want to keep those records; if not, we will purge them out of the data set.

[jeetamar11]

Hi rzdodson,

Can you explain this again please?

I need to calculate data for all days MTD till yesterday- so basically instead of going by today's date we can go by yesterday's date to select MTD data. Can we create something more simple with this logic ?

[jeetamar11 · Accepted solution]

This worked- DateTimeMonth([Case Created Date]) = DateTimeMonth(DateTimeNow())-1 AND DateTimeYear([Case Created Date]) = DateTimeYear(DateTimeNow())

Thank you for your help!