Back
Project

Creating burndown charts for Project using Power Pivot and Power Query

When you are managing a project and tracking the work done versus the work planned or the remaining time left, it is common to use a burndown chart. In this article, we show you how to create a report of all tasks for the last 90 days, using Excel 2013 and oData streams. This example will also introduce you to some key Power Query and Power Pivot functions.

Prerequisite:

Note: In this example we are going to build a burndown using the TaskTimePhaseDataSet table, but it can be done with any other table.

Use Power Query to gather data

In Excel, create a new workbook, and then choose the Power Query tab in the ribbon. In the Get External Data section, choose From Other Sources > From OData Feed.

From OData Feed Ribbon Location

Enter the address for your OData feed in the URL box, and then click OK.

If the address for your PWA site resembles https://<tenantname>.sharepoint.com/sites/pwa, the address you’ll enter for your OData Feed is https://<tenantname>.sharepoint.com/sites/pwa/_api/Projectdata.

For our example, we’re using https://contoso.sharepoint.com/sites/pwa/default.aspx

OData Feed dialog box

Excel will prompt you to authenticate with your Office 365 account. Select Organizational account and then enter your credentials.

Sign In dialog

Because we selected the higher level OData stream, Power Query is now showing the list of all the available tables. Select  the one that you want to build your report on. In our example, we use TaskTimePhaseDataSet. Then click Edit.

TaskTImePhasedDataSet

Select only the required properties

Power Query downloads a sample data set of approximately 100 rows. We are going to build our query based on this. As a best practice, trim your OData request to include only the properties you care about. For our example, we’ll only keep a few properties. We select the columns we do not need, and then remove them.

Removing columns that aren't needed

In our burndown chart, we will only use these properties: ProjectID, TimeByDay, ProjectName,  TaskActualWork, and TaskWork), and remove all other columns

Here is our new data source, with only the fields we care about:

Data Source with Relevant Fields

Load to the data model

To avoid saving data twice, make sure to set the data to load in the Data Model only. This can be done under Load Settings, at the bottom of the right column

Load Settings

Get tasks planned in the last 90 days

Next, we want to filter only for the tasks in the timeframe of our burndown. We chose 90 days here, but you can select different values.

We click on the down arrow on the header of TimeByDay, and then select Date/Time Filters > After.

Then, we specify a date in the box. The more recent the date, the faster the next step will be.

Filter Rows dialog

Note: Depending on the number of tasks you have, the wait between each step can be a few minutes long. This is how long it takes for Power Query to get your data and analyze it.

Now Power Query shows tasks with a TimeByDay date after 3/2/2014. We want to change this to make it take into account the current date.

In the query table, we change this query:

= Table.SelectRows(RemovedColumns, each [TimeByDay] > #datetime(2014, 3, 2, 0, 0, 0))

To this query:

= Table.SelectRows(RemovedColumns, each [TimeByDay] > (DateTime.FixedLocalNow() – #duration(90, 0, 0, 0)))

This will use today’s date in the OData query to filter on tasks that are less than 91 days old. The actual OData query will include a filter similar to this one:

…ProjectData/Tasks?$filter=( TaskTimePhaseDataSet gt datetime’11/10/2013′)’.​

 Get tasks due today or earlier

This is similar to what we did in the previous section, but this time, we’ll choose Date/Time Filters > Before.

Then, in the query, we’ll edit the query so that it changes from this:

= Table.SelectRows(FilteredRows, each [TimeByDay] < #datetime(2014, 6, 15, 0, 0, 0))

to this:

= Table.SelectRows(FilteredRows, each [TimeByDay] < (DateTime.FixedLocalNow() ))

This will ensure that we only retrieve tasks with a TimeByDay date equal to today or less.

Check your steps

At this point, in the applied steps area, we should see four applied steps: one for the source, one for filtering out unwanted columns, and one for each date filter.

Applied Steps

At this point, we are done with Power Query. We click Apply and Close to download the filtered dataset into the Data Model.

Note: You can learn more about Power Query glossary here.

Calculating values with Power Pivot

Our next step is to launch open Power Pivot and create a calculated field for the burndown report.

We start by clicking the Manage button on the PowerPivot tab in the ribbon

Go to PowerPivot

When Power Pivot opens, we click the Calculation Area button in the View section

Ribbon - Calculation Area

Total planned work for the period

We’ll begin by adding a calculated value of the sum of all planned work for this period. In the top left cell in the calculation area, we enter this formula:

Total Planned:=SUMX(CALCULATETABLE(TaskTimephasedDataSet, ALL(TaskTimephasedDataSet[TimeByDay])),TaskTimephasedDataSet[TaskWork])

Actual work done to date

Next, we need to have the actual sum of work that was done up to this date. In the cell below the Total Planned formula, we enter this formula:

ActualToDate:=SUMX(FILTER(CALCULATETABLE(TaskTimephasedDataSet, ALL(TaskTimephasedDataSet[TimeByDay])), TaskTimephasedDataSet[TimeByDay]<=MAX(TaskTimephasedDataSet[TimeByDay])),TaskTimephasedDataSet[TaskActualWork])

Planned work to date

Next, we need the actual amount of work that was planned to date. In the cell below the ActualToDate formula, we enter this formula:

PlannedToDate:=SUMX(FILTER(CALCULATETABLE(TaskTimephasedDataSet, ALL(TaskTimephasedDataSet[TimeByDay])), TaskTimephasedDataSet[TimeByDay]<=MAX(TaskTimephasedDataSet[TimeByDay])),TaskTimephasedDataSet[TaskWork])

Planned work

Next, we define the value that will be used for Planned Work. In the cell below the ActualToDate formula, we enter this formula:

Planned Work:=[Total Planned]-[PlannedToDate]

Actual remaining work

Next, we define the actual remaining work by using the Total Planned and ActualToDate formulae. In the cell below the Planned Work formula, we enter this formula:

Actual Remaining Work:=[Total Planned]-[ActualToDate]

Now that we are done with defining the calculations, our last step is to create a chart using Power View.

Power View burndown chart

We close the Power Pivot window, and then, on the Insert tab, we click Power View.

Insert Power View

In the Power View Fields list, we select Actual Remaining Work, Planned Work and TimeByDay to build a chart table in the Power View sheet.

Chart Table

We select this table, and then click Switch Visualization > Other Chart > Line.

Sample Chart

For our last Step, we expand the Filters list, and then drag the ProjectName property to it.

Burndown with Project Filter

And we now have a burndown chart.

Burndown charts in Project 2013

You can also build a similar chart (and others) for a single project by using the reporting features in Project 2013. This is an easy way to create a burndown chart if you are reporting on only one project.

In Project 2013, click the Report tab.

Project Client - Report Tab

 

Click Dashboards > Burndown to see your data in a burndown chart

Burndown Charts

 

You can find more about client reports in a previous blog post.