Excel for running totals of tasks opened and closed (like Asana progress chart)

Question

Asana (and lots of other apps, I suppose) has an export of tasks, one row per task, with a created date and a closed date. Closed date is blank for open tasks.

task id       created at        completed at
347081        11/21/18          03/05/19
458705        02/25/19       

I want to use the export to create a progress chart like the one in Asana (that used to be free but now isn't) to make a running totals progress chart. I'm struggling with getting a pivot table to do exactly what I want. Among other particular issues, I can't get a pivot table to do a running total across a year boundary. Is there a particular way that's required to do this?

Answer 1

So, the answer turns out to be a lot more complicated than I thought. It requires power pivots and DAX, a calendar table in the data model, measures, and some particular tricks to get it just right. Also, a bunch of the partial solutions I found online don’t deal with problems that occur if your data has missing dates in it (like, no issues opened or closed in June, for example).

This answer was quite helpful: https://community.powerbi.com/t5/Desktop/Running-Total-Count-Groups-with-Missing-Values/m-p/338133#M151293

Here's my solution https://www.dropbox.com/s/60b6bu93hg076ns/MyApp%201.0%20progress.xlsx?dl=0 to share with others who may have this problem. It generates a chart that looks like this:

To use the spreadsheet, you have to copy and paste your Asana export data (or any other columns of created and closed dates) into the sheet data (columns B and C, specifically) without wiping out the tables that are defined for those two columns. Then you have to do Power Pivot/Manage Data Model/Refresh All to get the data model to take the data you’ve copied into columns B and C. The chart is attached to the pivot table below it, so you can change the chart to monthly by removing “Date” from the Rows section of the pivot table. To see how the solution works, you have to look in the power pivot data model measures that are defined for the data model. The measures (Opened, Completed, etc) used in the Values part of the pivot table contain the DAX code that makes the pivot table work.

Columns B and C are defined as two separate tables because they have to be separate tables in the data model in order to both have a relationship to the Date column of the Calendar table that's also in the data model. There are Excel limitations on data models, tables, and/or relationships that prevent having B and C both be in the same table and/or have the solution data in a single pivot table for putting into one chart easily.