Automatically Reporting on Previous Calendar Month

This little snippet is used in a filter and automatically looks at the previous calendar month.

cast([Sales Team].[Client Contacts].[Start Date],date)
between
_first_of_Month(_add_months(CURRENT_DATE,-1))
and
_last_of_month(_add_months(CURRENT_DATE,-1))

If the date is left as a timestamp then the last of the month is 2013-04-30 00:00 which means that a client contact that occurs at 9am would not be picked up. Casting it as a date ensures that it gets picked up as expected.

Another option is to add 1 day onto the _last_of_month, but that’s a hack.

The _add_months function can accept a negative number, which turns _add_months into _substract_months.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s