Project Reporting Part 6 – Historical Data Reporting with Azure DevOps Analytics View

If your project team uses Azure DevOps to manage your project delivery, one thing that the built in dashboard widgets do not do well is the visualisation of historical statistics from the project. In this post we discuss how this can be achieved by using Analytics View and Power BI visualisation.

Project Reporting Part 1 – Crucial Contribution Project Team Makes in Reporting
Project Reporting Part 2 – Why Report on Delivery Data with Power BI
Project Reporting Part 3 – Reporting with Power BI for Azure DevOps
Project Reporting Part 4 – Azure DevOps Analytics View Data Model and Power BI Report Designs
Project Reporting Part 5 – Analysing Closure Trends with Azure DevOps Analytics View

In the following sections, we have pulled an “all history” analytics view from the Azure DevOps project called “Smart Track” as discussed in the previous posts. Also new tables are created based on the original Analytics View in order to e.g. aggregate values. This will be done by using common DAX Expressions available in Power BI.

Historical Snapshot of Project Data

The project “Smart Track” Iteration 1 started on 22 Dec 2018 and ended on 28 Dec 2018. Let’s assume you want to create a Power BI report showing you all the relevant project statistics (e.g. number of open bugs, closed user stories etc) for particular dates in the project iteration. You can do this relatively easily using Analytics View based on the “Date” field (see a previous post for an explanation of how “Date” works in an “All Histories” Analytics View).

For example, the Power BI report below provides a “Date” Slicer which allows the user to select a specific “Date” and display relevant statistics in the report. By selecting 3 dates (beginning – 20 Dec, middle – 24 Dec, and end – 28 Dec), you can see the number of work items in this project moving through the different states (New, Active, Test, Resolved, Closed). For example, as you move through the historical snapshots you will see the number of bugs increasing through the iteration period.

In the rest of this post we discuss how to put this report together based on the Analytics View called “All Work Items” pulled from Azure DevOps project named “Smart Track”.




The Slicer Provides the Report Page Context

The “Slicer” visualisation allows you to define the context of the Power BI report. Think of this slicer as a data filter. As we want to show snapshots of historical statistics from Azure DevOps Analytics View called “All Work Items”, we need to set the Slicer on this “Date” field of the “All Work Items” analytics view table. For example, if you select for the Date “28 December 2018”, no matter what charts you create on this report page, you will only be shown all work times with “Date” equal “28 December 2018” from the Analytics View.


Tracking all Work Item States in a Historical Snapshot

All Azure DevOps Work Items (e.g. User Stories, Tasks, Bugs etc) go through different “States” during its lifecycle from creation to completion. In Azure DevOps you can see this change by viewing the work item’s “State Graph”. For example the screenshot below shows a user story (id 59) which went through the different states starting from “New” to “Active” to “Resolved” to “Test” etc.


Very often project team get asked the question, “how many items are new?”, or “how many items are closed?”. To answer these questions for a particular historical snapshot, we can create a “Card” visualisation show the total count of work items in each states for a particular “Date”.


Simply pick the “Card” visualisation, set the Fields you want to count (e.g. Count of Unique Work Item Id), Rename this field to “Closed Work Items” to ensure it shows as the category label on the Card itself. In addition, I needed to filter the data set by “Iteration 1” (where I’m tracking my data), and the State is set to “Closed”. Note there is no need to filter for “Date” as it has already been provided as the report context by the “Date” slicer created earlier.


Break down of Work Item Types with States in a Historical Snapshot

I can also plot the total count of different states for each types of work items in the project by historical snapshots. For example the 3 donut charts below shows each work item types (user stories, tasks and bugs) broken down by the item states.


To create a donut chart, simply set the “Details” field to “State” and “Values” to aggregate as the “Count of Work Item Id”. If you want a donut to show each work item type separately, you will need to add a filter for e.g. “Work Item Type” is “User Story”.


Also, you can show more details of the breakdown by using a “Multi-Row Card” visualisation. Simply drop the fields you want to show and aggregate in the “Fields” of this visualisation.


Compare the Original Estimates, Remaining Work, and Completed Work for all User Stories in Historical Snapshots

In Part 1 of this blog post series I mentioned that there’s a lot of insights to be gained if the project team is willing to put in some administrative work to break down each user stories by children “tasks” work items in Azure DevOps, and follow up with progress by updating all tasks’ “Remaining Work” and “Completed Work”. A historical snapshot report in Power Bi then allows you to chart and compare estimates and actual efforts in a Line and Stacked Column Chart as below.

Assuming all child Tasks are linked to parent User Stories, you can set the “Shared Axis” as the unique “Parent Work Item Id” (id of the user stories). This aggregates values of all child tasks for each parent user story. The stacked columns of this chart I have chosen to plot “Completed Work” and “Remaining Work”, while “Line Values” shows the “Original Estimates”. As you change the “Date” slicer, this chart will show you a snapshot of historical estimate versus remaining and completed work for each parent user story.


Putting All of the Above Together …

You now have a Power BI report which allows you to control the view of historical snapshots you want to see based on the “Date” field in the slicer. I find this a powerful tool which allows me to look through the histories of all work items from Azure DevOps project and allows me to spot trends and issues as they move through the iteration.


1 thought on “Project Reporting Part 6 – Historical Data Reporting with Azure DevOps Analytics View

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s