How to create a dashboard chart showing the actuals (completed) vs planned (not completed) tasks?

Ulrich Anderhub 1

How to create a chart in a dashboard having two lines showing the actual number of finished tasks (completed) vs the planned number of not completed tasks (Not started, In Progress) on a timeline? Any help would be much appreciated.

Chris Malone

My initial thought is to create a metric sheet that you can use to count the number of tasks in each category via cross-sheet references and by whatever timeframe you want to look at (week, month, year, etc.) and then use a line graph or stacked bar graph to represent the data. However, if you can provide a sample set of your data, I may be able to help provide a better solution to your question.

Ulrich Anderhub 1

Hi Chris, thanks for your feedback!

This is my project plan where the data needed basically is stored...

Paul Newcome

In a separate sheet, you would use something like this:

Actual:

=COUNTIFS({Proj Plan Status Column}, @cell = "Complete")

Projected:

=COUNTIFS({Proj Plan End Date Column}, @cell< TODAY())

Ulrich Anderhub 1

Thanks Paul! The formulas to get the two values for the lines worked so far... But how can I make the formula values cumulative and put them on a timeline? This is what I try to achieve at the end...

Paul Newcome

IN your metrics sheet you would need to have a column that houses those dates then incorporate a date based range/criteria set in the COUNTIFS.

You would also need to record an actual completed date in your source sheet.

Ulrich Anderhub 1

Ok, got it. How would the formula look like than? Having this...

Paul Newcome

You would include a range/criteria set for the date to be less than or equal to the chart's date column.