Department capacity reporting
Hello,
I'm trying to find an effective way to report capacity by department in a dashboard. For example, I have multiple departments working on several projects with different start/end months. What would be the best way to report on this in a dashboard?
Answers
-
Hi @anuelle
The tricky thing here is that your dates are listed as text and not as a Date in a Date Column. This means that there isn't a way to easily check to see if a row is active in "March" if the Start says "February" and the end say "April".
What I would do is set up two helper date columns next to each of your Start and End text fields. Then you can use a formula to automatically populate the first of the month and year that you have written down.
Ex:
=IF([Target Start]@row = "January 2022", DATE(2022, 01, 01), IF([Target Start]@row = "February 2022", DATE(2022, 02, 01), IF([Target Start]@row = "March 2022", DATE(2022, 03, 01)... etc
See: IF Function / Create and Edit Formulas in Smartsheet / DATE Function
Once you have actual Dates to reference, I would build a Metric sheet that lists your Departments in a single cell down one column, then Text columns to represent each Month/Year.
You can set up a COUNTIFS formula to COUNT how many projects each individual Department is working on in each month, by checking to see if that month falls in the date range of the helper Date columns you have set up.
Ex to look in January:
=COUNTIFS({Who Column Source Sheet}, HAS(@cell, [Who Column]@row), {Start Date Column}, >= DATE(2022, 01, 01), {End Date Column}, <= DATE(2022, 01, 01))
This metric sheet can then be used as a source for graphs or charts in your Dashboard. Let me know if this makes sense or if you need help with either formula; I'd be happy to post screen captures as well if that would be useful.
Cheers,
Genevieve
-
@Genevieve P. Just came across this - would you be able to post screenshots? Thanks!
-
Hi @Aaron T.
Happy to! However can I clarify if your sheet is set up in the same way? Do you have a column with text dates instead of a Date type of column?
-
@Genevieve P. mine are setup with a Date type column, not text dates.
Essentially, I'm trying to build a dashboard to show month over month capacity for a specific team. I have a "Team" column, a project start date column, and a project end date column.
-
Hi @Aaron T.
That's awesome! Sounds like definitely a different set-up to the one shown above.
In your case, as long as the Team column is a Text/Number column or a single dropdown list, I would actually suggest simply using a Report to Group by the Team name, then you can show it in a Gantt view, sorted in the order of Start date. That will give you a view across months for each team.
Here are related Community posts:
Monitoring Workload without Resource Add-On
I would like to set up a calendar view < This one has a visual that shows what I mean above
Cheers,
Genevieve
