Adding Work Item Age to your Azure DevOps board using Power Automate

10 min readJun 23, 2023

The first in a series of three blogs covering how you can add flow metrics directly into your kanban board in Azure DevOps. Part one (this blog) will cover adding Work Item Age. Part two covers adding Cycle Time and part three will show how to add in your Service Level Expectation (SLE)…

Context

As teams increase their curiosity around their flow of work, making this information as readily available to them is paramount. Flow metrics are the clear go-to as they provide great insights around predictability, responsiveness and just how sustainable a pace a team is working at. There is however, a challenge with getting teams to frequently use them. Whilst using them in a retrospective (say looking at outliers on a cycle time scatter plot) is a common practice, it is a lot harder trying to embed this into their every day conversations. There is no doubt these charts add great value but, plenty of teams forget about them in their daily sync/scrums as they will (more often than not) be focused on sharing their Kanban board. They will focus on discussing the items on the board, rather than using a flow metrics chart or dashboard, when it comes to planning for their day. As an Agile Coach, no matter how often I show it and stress the importance of it, plenty of teams that I work with still forget about the “secret sauce” of Work Item Age in their daily sync/scrum as it sits on a different URL/tool.

This got me thinking about how we might overcome this and remove a ‘barrier to entry’ around flow. Thankfully, automation tools can help. We can use tools like Power Automate, combined with other sources, to help improve the way teams work through making flow data visible…

Prerequisites

There are a few assumptions made in this series of posts:

Within Azure DevOps (ADO) you have an inherited process template and access to edit this
You know how to generate a Personal Access Token (PAT) in ADO and have already done so with full or analytics read access
States and state categories in your process template are configured correctly
You have access to Power Automate

With all those in place — let’s get started!

Adding a ‘Work Item Age’ field to ADO

We first need to add a new field into our process template in ADO called Work Item Age. You need to also know the respective work item type(s) you want to do this for. For the purpose of simplicity in this blog we will stick to Product Backlog Item (PBI) as the work item type we will set this up for and be using an inheritance of the Scrum process template.

Please note, if you are wanting to do this for multiple work item types you will have to repeat the process of adding this field for each work item type.

Find the Product Backlog Item type in your inherited process template work items list
Click into it and click ‘new field’
Add the Work Item Age field — ensuring you specify it as an ‘integer’ type

That’s the easy part done, now let’s tackle the trickier bits…

Understanding how Work Item Age is to be calculated

From Microsoft's own documentation, we can see that in ADO their Cycle Time calculation is from when an item first enters an ‘In Progress’ state category to entering a ‘Completed’ state category:

Source: Cycle Time and Lead Time control charts — Azure DevOps Services | Microsoft Learn

Therefore, we can determine that for any items that have been started but not completed, the Work Item Age is calculated as the difference, in calendar days, between the current date and the time when an item first entered the ‘In Progress’ state category, also known as the InProgressDate.

It is not the intent of this blog to get into a debate about adding +1 days to an item as there are no instances where an item has taken 0 days to complete — for that we have Drunk Agile ;)

Ultimately, calculating Work Item Age this way still aligns with the definition as set out in the kanban guide as it is still “the amount of elapsed time between when a work item started and the current time.”

Now let’s jump into the automation…

Automating Work Item Age

We start by creating a query in ADO of all our current ‘in progress’ items. The complexity of this will of course vary depending on your ADO setup. For this we are keeping it simple — any PBI’s in our single ‘In Progress’ state of Committed:

Please ensure that Work Item Age is added as one of your columns in your query. It needs to be saved as a shared query and with a memorable title (sometimes I like to add DO NOT EDIT in the title).

Next we go to Power Automate and we create a Scheduled cloud flow:

We are going to call this ‘Work Item Age’ and we will want this to run every day at a time that is before a teams daily sync/scrum (e.g. 8am).

Once you’re happy with the time click create:

Next we need to click ‘+ new step’ and add an action to Get query results from the query we just set up:

Please ensure that you input the relevant ‘Organization Name’ and ‘Project Name’ where you have created the query:

Following this we are going to add a step to Initialize variable — this is essentially where we will ‘store’ what our Work Item Age is which, to start with, will be an integer with a value of 0:

Then we are going to add an Apply to each step:

We’ll select the ‘value’ from our ‘Get query results’ step as the starting point:

Then we’ll add a Get work item details step. Here we need to make sure the ‘Organization’ and ‘Project’ match what we set out at the beginning.

For Work Item Type we need to choose ‘Enter Custom Value’:

We can then choose ‘Work Item Type’ and ‘ID’ as dynamic content from our ‘Get query results’ step previously:

With the end result being:

Next we need to add a HTTP step. This is essentially where we are going to get the InProgressDate for our items:

You’ll need to set the method as ‘GET’ and add in the the URL. The first part of the URL (replace ORG and PROJECT with your details) should be:

https://analytics.dev.azure.com/ORG/PROJECT/_odata/v3.0-preview/WorkItems?$filter=WorkItemId%20eq%20

Add in the dynamic content of ‘Id’ from our Get work item details step:

After the Id, add in:

&$select=InProgressDate

Which should look like:

You’ll then need to click ‘Show advanced options’ to add in your PAT details. Set the authentication to ‘Basic’, add in a username of ‘dummy’ and paste your PAT into the password field:

Then we need to add in a Parse JSON step:

This is where we are essentially going to extract our InProgressDate.
Choose ‘body’ as the content and add a schema like so:

{
    "type": "object",
    "properties": {
        "@@odata.context": {
            "type": "string"
        },
        "value": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "InProgressDate": {
                        "type": "string"
                    }
                },
                "required": [
                    "InProgressDate"
                ]
            }
        }
    }
}

Then we need to format this how we want so it’s easier to do the date difference calculation. Add a Compose step:

Rename this to be Formatted InProgressDate and with the following as an expression:

formatDateTime(body('Parse_JSON')?['value'][0]['InProgressDate'], 'yyyy-MM-dd')

Then add another Compose step, this time to get the Current Date, which should be an expression like so:

formatDateTime(utcNow(), 'yyyy-MM-ddTHH:mm:ssZ')

Then we will add one more Compose step to calculate the Date Difference, which is the following expression:

div(sub(ticks(outputs('Current_Date')), ticks(outputs('Formatted_InProgressDate'))), 864000000000)

This is essentially doing a (rather long-winded!) date difference calculation. This appears to be the only way to do this type of calculation in Power Automate.

Then we need to add a step to Set variable, which was something we established earlier on to store the work item age:

Here we just need to choose the same variable name (ItemAge) and use the ‘outputs’ from the previous step (Date Difference) as the dynamic content:

Final step is to populate this on the respective work item in ADO. To do this, search for an Update a work item step:

Then you will want to populate it with the ‘organization name’ and ‘project’ you’ve been using throughout. You also need to ensure you add in ‘Id’ for the Work Item Id and ‘Work Item Type’ from your steps previous:

Then you need to click ‘Show advanced options’ and add ‘Work Item Age’ into other fields and choose ‘ItemAge’ as the value:

Then hit save and the flow is created (ensure all your step names match the below):

Then it’s best to do a test run, which you can do by clicking Test, select ‘Manually’ and then click Test > Run Flow:

Clicking ‘Done’ will take you to a page that will show you if steps have been successful and which are in progress:

You can also view the ‘Run history’ to see if it is successful. Please note the amount of in progress items will impact how long the flow takes:

Once run, if you check back into Azure DevOps and your query, you should now see your Work Item Age field populated:

Making this visible on the Kanban board

The final step is to make this visible on the Kanban board. To do this we need to go into our board settings and find the respective work item type. Under ‘Additional fields’ you’ll want to add Work Item Age:

Then, when your board refreshes, you will see the Work Item Age for your ‘In Progress’ items:

Ways you could take it further

Now, teams who are using the board as part of their daily sync/scrum also have the age of work items visible on the cards themselves. This allows them to make informed decisions about their plan for the day, without having to flip between different tools/links to view any charts.

You can then take this further, for example adding styling rules for when items go past a particular age:

Similarly, you could also leverage the swimlane rules functionality that was recently released and create a swimlane for items a team should be considering swarming on. This could be where items are close to exceeding a teams’ forecasted cycle time or Service Level Expectation (SLE):