Tracking your Agile project progress using Azure DevOps and Flow - The Sprint Burndown Chart

Microsoft Azure DevOps is a great tool that allows you to manage your Agile projects.  It allows you to easily track most of the information a scrum master or product owner are currently interested in, such as your product backlog, sprint backlog, burn down chart, and more.

If you're new to Agile project management, I recommend you have a look at a book I recently published on this topic called Agile Office 365.  Besides providing you with an overview of Agile project management using the Scrum methodology, it provides you with lots of information on how to manage your Office 365 projects using these methods. 

Some of the information tracked in Azure DevOps, such as the sprint burndown chart and current sprint backlog are often of interest to the clients to know how the sprint work is progressing.  Though you can extend Azure DevOps access to youir clients, this is not always desirable.  In this 2-part blog, I will discuss how I was able to bring key information from AzureDevOps into a SharePoint dashboard.

The Sprint Burndown Chart

Sprint Burndown charts are used to track the remaining work within the given sprint.  It typically looks like a downward trending graph with some ideal line.

The way the graph is generated is by summing the remaining work each day(y-axis) and plotting it against the timeline (x-axis).  Azure DevOps provides you with the ability to query various pieces of information, but there doesn't seem to be a simple burndown chart export.  So, here's how I was able to  achieve it.

The Ingredients

To build out the solution, I used 4 technologies from the Office 365/Azure stack
  • Azure DevOps
  • SharePoint list
  • Flow
  • PowerApps

Azure DevOps

This is an obvious one, as I'm basing my premise of this article that the project is being managed via Azure DevOps.  I am not going to get into the details of how to use Azure DevOps, as there are some great references already out there, such as Introducing Azure DevOps.

One thing I do want to touch on is a custom query I had to create for the solution.  Each epic, feature, user story, task, and bug in Azure DevOps contains a lot of data associated with it and you can create very complex queries to get a report of what you need.  In my case, all I cared about is a list of Tasks in my current sprint and the Remaining Work for each task.  When you build the query, there are two clauses I needed to include:

(WorkType = Task) AND (Iternation Path = @CurrentIternation)

Setting up this query provided me with a list of all the tasks within the current sprint.  The only column I needed to capture was the Remaining Work.

This query, which I called Hours Remaining is a key component to my Flow that I'm discussing further down in this article.

SharePoint List

The sprint burndown chart data is essentially tabular in nature.  You can store it in many ways - Excel, SQL table, SharePoint list, etc.  As my ultimate goal was to provide users with the sprint burndown chart on a SharePoint project site, it made sense to capture the information in a SharePoint list on that site.  The only columns I used were:

  • Title - to display the day number (e.g. Day 1, Day 2, ...)
  • Hours - to capture the Remaining Work for that day
  • Date - to capture the date of the remaining work


This is where all the magic happens.  The Flow is responsible to get information from the Azure DevOps query and update the SharePoint list accordingly.  In order to do that, the Flow is broken down into three parts:
  1. Determine if it's the start of the sprint
  2. Get the information from Azure DevOps
  3. Update the SharePoint list
In my case, step 2 was done by a separate Flow as Azure DevOps was on a different tenant.

Determine if it's the start of a new sprint

To know the start of a new sprint, I needed to establish the start date of the of the project and duration of each sprint (3 weeks, 21 calendar days).  With that information, I used a Compose function that would calculate the remainder of dividing the number of days since the project began to today.

The expression used determines the time in ticks, which allowed me to compare the current time to the start of the project.


ticks(variables('Project Start'))),864000000000),21)

If the remainder was 0, then it meant that the day is a multiple of 21, which meant that it was the start of a new sprint.  In that case, I deleted all my SharePoint list items and created new ones with the correct date applied for each sprint day.  

The Date field was calculated by adding 1 day each time in the Do-until loop. using the following expression:

formatdatetime(adddays(utcnow(),variables('Counter')),'MMM dd')

With the SharePoint list refreshed, it was now ready to receive the daily Remaining Work.

Get the information from Azure DevOps

As mentioned above, in my particular case, the Azure DevOps instance was managed by my company, while the SharePoint dashboard for the project site was on the client's tenant.  So I needed to do some cross-tenant work here.  To get the Remaining Work from my Azure DevOps tenant, I built a separate Flow with an HTTP end point that called my Azure DevOps custom query called Hours Remaining (discussed earlier in this article).

Once I got the remaining work, I calculated the total amount of hours remaining, and returned the total value to my calling function.

Update the SharePoint list

Now that I got the Remaining Work back, I was able to update the SharePoint list for the specific day.


The last step in my solution was to create a PowerApp to display the burndown chart for me.  Although somewhat limited in functionality today, I was able to use the charting functionality provided with PowerApps to give me the desired result.

All I had to do was to add a Line Chart control to the page, connect the SharePoint list as a data source, and map the Title and Remaining Work fields to the chart.

Putting it all together

Now with the PowerApp in hand, I was able to add a PowerApp web part to the SharePoint page to provide my clients with the information they required.  As a bonus, users were able to view the burn down chart directly on their mobile app if they desired.

No comments:

Post a Comment

Employee Check-in Simplified using Power Automate

Company employees who move between offices are often faced with a tedious process of having to check-in at the security desk to get a tempor...