Placeholder

Customer Forum

Reporting on opportunities closing this month or later

Workbooks Support Posted: 2011-06-06 14:18

I'd like to report on all my opportunities that are closing anytime this month onwards.  I can see how to apply a criteria to show all the ones closing in the future but don't know how to include ones with a data earlier this month.  Is this possible?

Workbooks Support Posted: Thu, 06.06.2013 - 14:41

Hi.  Yes, it is possible to do this by using an IF statement to create a column showing whether or not the close date is on or after the first day of the current month and then using a calculated criteria to limit the results to only those Opportunities that do have a close date on or after the first day of the current month.  The steps required are shown below.

First, add a calculated column to your report called 'This Month Onwards' using the following IF statement.  (You can call the column whatever you like, this is just an example):

  • IF(close_date>=CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE), 1, 0)

[For information, this formula is saying that if the close date on the opportunity is greater than or equal to the first day of this month, display a 1 and if it isn't, then display a 0.]

Next, open the criteria tab and add a calculated criterion to return just the rows where there's a 1 in the column called 'This Month Onwards' as shown below (click to enlarge):

This will now display only those records with a close date of anytime on or after the first day of the current month so as each new month starts, any records closing in the previous month will no longer be shown.  This theory can be adapted for use with other reports, not just opportunities.  Of course, you can still apply other criteria to narrow down the results further.

Remember, you can 'hide' columns in your reports so you may want to hide the column called 'This Month Onwards' to make your report look a bit neater.