Report Builder - Pull projects completed one year ago (month)

I would like to auto-run a report of projects completed over the course of a month one year prior. (i.e., the report would tell me on August 15, 2017 what projects were completed between August 1 and August 31 last year).

I plugged in report builder that the column I want it to filter by (Actual Completion Date) should be "Actual Completion Date is within the last 381 days." But it won't let me also use criteria of "Actual Completion Date is within the last 340 days". It's defaulting to "OR", which means it's pulling way more data than I need. I don't want to enter specific dates, because I want the report to auto-generate each month. That might be my default option, though (12 reports).

I feel like there should be an easy solution for this, but I'm not coming up with anything. I hope someone can help. I attached a screen grab. Thanks in advance.

Screen Shot 2017-07-19 at 4.14.20 PM.png

Find more posts tagged with

Reports

Project Management

Comments

Shaine Greenwood

Hi Laura,

Your solution of 12 reports seems to be the best way to go, as there currently isn't criteria that can get rows with the same month but prior year.

Another solution would be to add an IF statement to your sheets that will check a box if your date column is of the same month but at the prior year. Here's an example of this type of IF statement:

=IF(AND([Due Date]4 => DATE(YEAR(TODAY(-365)), MONTH(TODAY()), 1), [Due Date]4 =< DATE(YEAR(TODAY(-365)), MONTH(TODAY()), 31), 1)

PROS:

You could set your report criteria to only pull in rows that have the box checked, meaning you'd only need to create one report and in the long run it would be more dynamic.
Creating the report builder criteria is more simple, you'd be using the "What?" criteria to pull in rows that have a checked box in a checkbox column.

CONS:

Initial setup takes more time and adds more to your sheet—you'd need to create a checkbox column and add a formula similar to my example there.
You'd need to open the sheet and save it for the formula to update with the most current results, especially at the beginning of the month. This is a downside of the TODAY() function.

Hope this helps! Let me know if you have any questions.

Laura L

I appreciate the help, Shaine! I may have to go with 12 reports, as the report is pulling from 250+ sheets. It isn't efficient for me to add that formula to all sheets, then open them all to update with most current results as you noted. Thank you.