# Notion Answers

Help between Notion users

It's free & easy

### Get answers

Answers, votes & comments

### Vote and select answers

Receive points, vote and give the solution

1vote

# How to count the number of active projects each month?

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?

## 1 Answer

2votes

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.

0vote

### Jaap commented Nov 21

Thanks for the answer! The trick of having a linked property with all projects works well to count the projects each month. I can now even list the active projects each month by using a formula to filter the the projects. However, I don't see yet how I can use this to solve the second half of my question. How do I get an overview to see how many active projects a team has each month. (teams are in a database linked to the project database)

0vote

### Martin_SystemsHill commented Nov 22

Hey, the step two would be to create formulas in each month column to take the range from each of the years and compare it to the range of the projects related to that year and sum up the ones that match that condition.
This would give you the number of projects for each month.

1vote

### Jaap commented Nov 22

Hi, Thanks for the help. Based on your answers, I found a solution which sort of works.

I now have

a project database with a row for each project and properties

• starting an end date
• number of FTE
• relation to team database

a team database with a row for each team and properties

• relation to project database

a time database with a row for each month and properties

• starting and end date for each month
• a relation property to the projects database linking all projects to every month (I use a template project to automatically link new projects to all months)
• a column showing the active projects that month (formula filtering the linked project by date (prop("All Projects").filter(and((current.prop("Start Date")<=prop("End date")),(current.prop("end date")>=prop("Start Date"))))))
• a column showing the required FTE that month (formula summing the FTE property of each project in the active projects column (sum(prop("Active Projects").map(current.prop("FTE")))))
• a column for each team showing their active projects that month (formula filtering the active projects by team name (prop("Active Projects").filter(current.prop("team").format().contains("teamname"))))
• a column for each team showing the FTE of their active projects each month.

Some observations:

• it works!
• If the team name is altered you manually have to change the filter.
• It would be nice if notion would provide this out of the box. Ideally with some graphs.

1vote

### Martin_SystemsHill commented Nov 22

Hey, great job for making it work! Yes, I agree it would be nice to have this as a native function in Notion. I am hopeful this comes soon.

## Please log in or register to answer this question.

### Extend Notion Powers

...

Welcome to Notion Answers, where you can ask questions and receive answers from other members of the community.

Please share to grow the Notion Community!