Viewing team capacity over time

I am trying to see my team's capacity over multiple projects for each week to see who will have capacity to take on new projects. In the past I've done it by hand to look like this and I know in my mind the demands of each phase for a given project. This is quickly becoming unsustainable as we grow.


I have a task list for each project in smartsheet with the person responsible assigned, and we could add a % of capacity column where it shows how much of a person's capacity the task will take on those days. I was hoping to have a report that pulls in tasks from each sheet, grouped by person and sum of % capacity.


The part that is tripping me up is how to see it per week or day? Any ideas?

Answers