Question

I have a database with projects. Each project has a starting date and an end date. I would like to know for every month of the coming 2 years how many projects are active that month.

The only method I could come up with is to manually add a formula column for each month to my project database and use a formula to see if the project is active that month. However, this solutions scales badly if I want to use the information in other databases.

For instance, If I would like to use that information in my database with people to see how many active projects they have each month, I would then need to create these additional columns also in my people database. The same holds for my teams database etc.

Is there a better way?

Answer 1

Hi, probably the best way I would approach it is to make a database called "Number of Projects" with a row for each year and these columns

• 12 properties for each month o a year (it would include a formula to calculate the number of projects running/planned for that month)
• date property for the years range
• Relation property for that would have 2 way relation to all the active/future projects (an automation on the project database side would then create a connection to all the years created here for every new project created)

You would have to make an outlook for some years ahead and you would have to reset the automation after that amount of years but I do not think that would pose a problem if you created the "Number of projects" database for 10+ years at the beginning.