We need a way to provide counts of projects in various statuses each month through a calendar year; for example...how many projects were in progress in the month of Jan, how many projects were completed in the month of Feb, etc.
I have a column for each month in a calendar year and rows representing the metrics we need to capture (see below image). The issue is that project statuses change over time; a project that was in progress in Jan and completed in Jun was technically in progress Jan, Feb, Mar, Apr, May and should be counted as such. The data should count that project as being "in progress" each month but, once the status of that project changes to "completed" in Jun, the counts in the previous month decrease because the status is no longer "in progress".
Today, I use formulas to count everything "in progress" for each metric and then copy/paste the values each week in that month; This is a manual effort and I would love to automate but am struggling with how to do this. I may be able to use the new Record Date workflow functionality to capture the dates when a project goes to "in progress" and leaves "in progress" but what would the formula look like to calculate and retain that count each month? It can't be based on the current status of the project...it has to be based on the status during that month. I basically need a "snapshot" of the data for each month. Please help...any and all ideas appreciated!
