Dashboard Chart Summarizing Project Plan

Fred Huang

Hi Community - we have a basic requirement to show the number of tasks forecast to finish each week against the baseline plan. For example, this is my Project Plan:

Task Name Finish Plan Finish

Task 1 3/1/2021 2/22/2021

Task 2 3/8/2021 2/22/2021

Task 3 3/8/2021 2/22/2021

Task 4 3/15/2021 2/22/2021

I would like to generate a line chart with two lines on it like this:

I can only think of two ways to do this right now, both of which are essentially to manually generate the cumulative counts for Finish and Plan Finish for each day in the chart's x-axis. Either way a ton of extra columns in the Project Plan sheet or with a separate Grid sheet that looks like this:

Is there a better way to do this?

Paul Newcome

Do you already track the actual vs estimated finish in the project plan?

Fred Huang

Yep! I have two date columns in the Project Plan, one for each.

Paul Newcome

Then you should be able to just build out the metrics sheet as you have displayed with the dates in one column and then COUNTIFS formulas in the other two columns to pull the counts. You shouldn't need a bunch of additional columns on the source sheet.

=COUNTIFS({Project Plan Date Column}, @cell = [Metrics Sheet Date Column]@row)

Fred Huang

Yes, I can certainly do that, but that's more a brute force method. For example, as the Project Plan's timeline extends, I'll have to remember to go into the metrics sheet and fill down with additional rows for the weeks of interest. Was just curious if there is any better solution in SmartSheet right now - it's basically a mash up between count and group by week, for example, and pretty common in more powerful business intelligence/dashboarding tools like Tableau.

Paul Newcome

One thing I have done to accommodate this is to "prefill" rows. Go ahead and put dates into WAY more rows than you need. Extend it beyond anything logical for your project. Insert another date column that will be used for labels and enter something along the lines of...

=IF([Date Column]@row <= MAX({Source Sheet Date Column}, [Date Column]@row)

This will leave any rows blank for those dates that are beyond what are in your project plan, but will automatically pull the date over if the timeline becomes extended.

Then you can nest your COUNTIFS in an IF statement

=IF([Label Date Column]@row <> "", COUNTIFS(......................))

This will leave the count column blank if the date column is blank and shouldn't show up on your charts until (if) those additional dates are populated.