Hi there,
I'm working on an overview to determine the workload/capacity of my online marketing team. In Airtable I have 3 different tables:
- A table with our clients. This consists of multiple columns of data, but for this question only the client name is relevant.
- A table with all the individual tasks made up as rows. A task consists of a link to the table with the client names, the service we provide (SEO, SEA, Meta, CRO etc.), the amount of hours we have for each task, a link to a different table that contains our individual team members so we can link each row/task of data to a specific person, a multi select column containing each month of the year since some tasks run for 1-3 months while others apply to the entire year, and a multi select column indicating if it's a monthly recurring or a one-time task.
- A table with our team members. This table has the name of our team members as rows, a column with a formula that pulls in the sum of the total amount of hours from recurring tasks, a column with a sum formula that pulls in the total amount hours of one-time tasks, a column with the monthly availability in hours for each team member based on their contract and a linked record that pulls in the months from the table with tasks.
The next step for me is to get insight into the capacity of each teammember for a specific month. Within the table containing the team members I created a new view, for example "September". I tried applying a filter where months (from tasks) has any of September. However, the data in the columns recurring hours and one-time hours seem to remain the same and are not really influenced by the filter.
I also tried using the date column format in the table with tasks. Creating a start-date column and an end-date column. With the use of a filter in the table with team members where the start date is on or before 1-09-2023 and end date is on or after 30-09-2023. However, this also didn't work since the amount of recurring and one-time hours remained the same.
Therefore I'm hoping someone here can help me out 😄 Please let me know if you need any additional information.
