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:

• Excel 2013 (standalone or as part of Office 2013 Professional Plus or Office 365 ProPlus)
• Microsoft Power Query for Excel
• Power Pivot and Power View enabled in Excel
• A Project Online tenant environment with project data

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.

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

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

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.

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.

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:

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

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.

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.

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

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

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.

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.

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

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

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.

 

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

 

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