How to track Azure work items that have been added into the sprint after the iteration start date?

Question

Folks,

First of all, this question is based on the premise that I´m connecting Azure devops data through the Analytics View and all fields available were selected to the view.

I want to track whether the development team stuck to the plan or worked on backlog items that have been created OR have been added into the sprint after the sprint was started. But I have no clue on how to do it. Apparently is looks easier to write an IF statement for the ‘Creation Date’ criteria.

But how to effectively identify when an item has been added after the sprint start?

Answer 1

Sorry for resurecting an old post, but my client's manager asked me the exact same question. I was able to do it using PowerQuery (which can be viewed in either Excel or PowerBI)

I have parameters in my query but you can change it with your actual value directly in the string.

So I have a first query that gives me the data from Azure DevOps analytics wokitem snapshot. This gives the history of your workitems. I called that one "Sprint Burndown".

let
    Source = OData.Feed( "https://analytics.dev.azure.com/"&DevOps_Company&"/"&DevOps_Project&"/_odata/v4.0-preview/WorkItemSnapshot? 
        $apply=filter(
            (WorkItemType eq 'User Story'
            or WorkItemType eq 'Bug'
            or WorkItemType eq 'Action Plan'
            or WorkItemType eq 'Issue')
            and DateValue ge Iteration/StartDate
            and DateValue le Iteration/EndDate
        )
        /groupby(
            (DateValue,State,WorkItemType,Priority,Area/AreaPath,Area/AreaName,Iteration/IterationPath,Iteration/StartDate,Iteration/EndDate,StateCategory,WorkItemId,Title,AssignedTo/UserName),
            aggregate($count as Count, StoryPoints with sum as TotalStoryPoints)
        )
        &$orderby=WorkItemId asc, DateValue asc"
, null, [Implementation="2.0"]),
    #"Area expanded" = Table.ExpandRecordColumn(Source, "Area", {"AreaName", "AreaPath"}, {"Team", "AreaPath"}),
    #"Iteration expanded" = Table.ExpandRecordColumn(#"Area expanded", "Iteration", {"IterationPath", "StartDate", "EndDate"}, {"IterationPath", "StartDate", "EndDate"}),
    #"AssignedTo expanded" = Table.ExpandRecordColumn(#"Iteration expanded", "AssignedTo", {"UserName"}, {"AssignedTo.UserName"}),
    #"Split IterationPath" = Table.SplitColumn(#"AssignedTo expanded", "IterationPath", Splitter.SplitTextByEachDelimiter({"\"}, QuoteStyle.Csv, true), {"Iteration Folder", "Sprint"}),
    #"TeamSprint added" = Table.AddColumn(#"Split IterationPath", "TeamSprint", each [Team] & "\" & [Sprint]),
    #"correct types" = Table.TransformColumnTypes(#"TeamSprint added",{{"Iteration Folder", type text}, {"Sprint", type text}, {"TotalStoryPoints", Int64.Type}, {"DateValue", type date}, {"TeamSprint", type text}, {"StartDate", type date}, {"EndDate", type date}}),
    #"change category" = Table.ReplaceValue(#"correct types","Resolved","InProgress",Replacer.ReplaceText,{"StateCategory"})
in
    #"change category"

Since that query may have many other purpose for you, I let it be and create a second query for the filtering. I will call that one "Sprint Changes"

let
    /*Start from burndown data with sprint informations*/
    Source = #"Sprint Burndown",
    /*Keep only data from first day and last day of sprint (today if sprint is not yet finished)*/
    #"Just Start and End" = Table.SelectRows(Source, each ([DateValue] = [StartDate] or [DateValue] = List.Min({[EndDate],DateTime.Date(DateTime.LocalNow())}))),
    /*Removed unuseful columns*/
    #"Clean Dataset" = Table.RemoveColumns(#"Just Start and End",{"State", "Priority", "StateCategory", "Count", "AreaPath", "Iteration Folder", "AssignedTo.UserName", "TotalStoryPoints"}),
    /* Remove Backlog */
    #"Filtered Rows" = Table.SelectRows(#"Clean Dataset", each ([Team] <> DevOps_Project)),

    /*Find all duplicaes in the same sprint/team */
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"TeamSprint", "WorkItemId"}, {{"FoundItems", each Table.RowCount(_), Int64.Type}}),
    /* Keep only unique items */
    #"Find unique" = Table.SelectRows(#"Grouped Rows", each ([FoundItems] = 1)),

    /*Remove all duplicaes in the same sprint/team */
    #"Keep Unique" = Table.NestedJoin(#"Filtered Rows", {"WorkItemId","TeamSprint"}, #"Find unique", {"WorkItemId","TeamSprint"}, "matches", JoinKind.Inner),

    /*If there at start but not anymore = REMOVED, If not there at start but there now = ADDED*/
    #"Labeled Event" = Table.AddColumn(#"Keep Unique", "Event", each if [DateValue] = [StartDate] then "Removed" else "Added"),
    #"Removed merge artifacts" = Table.RemoveColumns(#"Labeled Event",{"matches"})
    
in
    #"Removed merge artifacts"

You can then use the result of that last query to identify which items were added or removed from your original planning.

Answer 2

Alternative option (2023): Sprint Burndown Chart widget directly within the Azure Dashboard can provide insight on this.

If you don't use Sprints (e.g. team using Kanban rather than Scrum), consider simply having Sprints as the Month names, or representing the quarter.

Example burndown widget in Azure (and yeah ... i know, this team obviously has some challenges this sprint, lol :)

Answer 3

Unfortunately,there is not a built-in report can be generated to get what you want. Since TFS/Azure DevOps is an effort tracking system and not a time tracking system it is a loosing battle to try and track billable time as part of TFS/Azure DevOps.

Agile projects don't focus on how long individual tasks take(Same concept as when items first added to a sprint) -- they focus on how much value the development team is providing over the course of a set period of time. One thing might be estimated low, one task might be estimated high, but it ultimately doesn't matter as long as the team delivers what they committed to deliver.

About Scrum, suggest you take a look at this related question: Reporting on completed sprint

If you insist on achieving this, you could use some 3-rd party extension such as Imaginet Time Sheet & Timetracker. For example, about time tracker, it is fully integrated into TFS, which allows tracking directly against workitems and offers data export as well as automatic filling of the fields mentioned above. With the help of the extension, project manager could have all times based on rules automatically reported back into MS Project.