Tracking sprint velocity to specific status

[rbelmont]

Hi all,

I’m trying to create a report that:

1. Tracks the number of points moved to Dev Complete over the previous number of sprints
2. Enables me to filter down to specific developers to see how many points they’re moving to Dev Complete in each sprint

I found another community post that got me part of the way there, but has some unexpected behavior; the filtering (via dimensions as Pages) seems wildly inconsistent. I used the following formula to calculate points moved to Dev Complete per Sprint:

Sum(
  Filter(
    -- filter issues having a particular transition during an active sprint
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateBetween(
      ([Measures].[Transition to status first date], [Transition Status].[Dev Complete]),
      [Sprint].CurrentMember.get('Start date'),
      [Sprint].CurrentMember.Get('End date') )
    -- and issues were in a selected sprint
    AND [Measures].[Issues history] > 0
  ),
  [Issue].CurrentMember.get('Story Points') )

Filtering the results by Assignee gives weird results because the current Assignee is typically not the one who moved it to Dev Complete. I added a new field called “Primary Developer” and populated it with the correct users for all relevant tickets, but it’s working as expected for some users and not others (a Primary Developer I can confirm has tickets moved to Dev Complete within recent sprints shows up with 0 points moved when I add the filter).

Does anyone know why I’m experiencing this issue, or have a better way for me to accomplish my goal here?

Thanks,
Ryan

[zane.baranovska]

Hi @rbelmont,

You may try another approach for the calculation and check the Story Point value when the issue changed status to “Dev Completed”.

For the calculated measure, use a tuple of measure “Story Points added” and “Transition Status” you are interested in. The expression might look like this:

( [Measures].[Story Points added],
 [Transition Status].[In Progress],
 [Transition Field].[Dev Completed])

Use the dimension “Transition Author” to see a user who moved issues to Dev Completed status. This dimension is designed to work with change history and show transition status changes. On the other hand, dimension “Assignee” represents the issue field Assignee current or past value according to the selected measure.

A similar solution:

Story points transitioned to UAT status

Hello, I am trying to create a bar chart that shows how many story points were transitioned to UAT status per sprint. I already have a calculated field for the amount of issues: ( [Measures].[Transitions to], [Transition].[Testing => Ready for UAT ) All issues have the field story points populated. Now I need to calculate the sum of story points of the transitioned issues. Any help is appreciated!

Best,
Zane / support@eazyBI.com

[rbelmont]

Hi @zane.baranovska,

I’m trying to use the following tuple based on your recommendation:

( [Measures].[Sprint Story Points added],
 [Transition Status].[Dev Complete],
 [Transition field].[Status])

However, “Sprint Story Points added” ("Story Points added doesn’t exist in my EazyBI) doesn’t seem to capture the right information either; when I add just “Sprint Story Points added” to a report I get 105, 132, 111, 80, 104 for the last 5 sprints, and Excel analysis of the same period shows 171, 223, 207, 240, and 177 just for moved to Dev Complete alone. Trying to create the tuple you described results in 0, 9, 3, 0, 0 for that period. What am I doing wrong here?

[zane.baranovska]

@rbelmont, could you share the report definition and a screenshot of the report so I can see what else is on report rows, columns, and pages and how it looks to you?

If you do not want to share this information here, you may send it to eazyBI Support.

[rbelmont]

Hi @zane.baranovska,

Took a long break from this report, getting back to it now and would really appreciate some input/assistance. Right now I’m trying to create a measure of velocity that is an aggregate of:

1. Points moved from In Progress → Dev Complete
2. Points moved from In Progress → Lead Review (skipped dev complete)
3. Points moved from To Do/In Progress → Closed (skipped other steps)

I’ve mostly got this working, but I’m running into some double counting issues.

• If a ticket goes through more than one of these transitions in a time period (e.g. In Progress → Dev Complete multiple times in a sprint) the story points are double counted for that sprint.
• If a ticket goes through more than one of these transitions in multiple time periods (e.g. goes from In Progress → Dev Complete in Sprint 1 and Sprint 2) the points are similarly double counted

I only want the points for each ticket to be recognized once total, not each time one of these transitions occurs. Is there any way to accomplish this?

Here is the report definition:

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Dev Velocity Tracking",
     "folder_name": "Ryan WIP",
     "result_view": "table",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Dev Sprint Velocity]"],"members":[{"depth":0,"full_name":"[Measures].[Dev Sprint Velocity]","format_string":"#,##0","report_specific":true,"name":"Dev Sprint Velocity","calculated":true}]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time].[DW Sprint 8]","[Time].[DW Sprint 9]","[Time].[DW Sprint 10]"],"members":[],"bookmarked_members":[]}]},"pages":{"dimensions":[]},"options":{"nonempty":"rows"},"view":{"current":"table","maximized":false,"table":{}},"calculated_members":[{"dimension":"Measures","name":"Dev Sprint Velocity","formula":"[Measures].[Story Points to Dev Complete] + [Measures].[Story Points to Lead Review] + [Measures].[Story Points to Closed]","format_string":"#,##0"}]}
  } ],
  "calculated_members": [{"name":"DW Sprint 10","dimension":"Time","formula":"Aggregate(\n[Time].[Day].DateMembersBetween('Jun 27 2022', 'Jul 10 2022')\n)","format_string":""},{"name":"DW Sprint 9","dimension":"Time","formula":"Aggregate(\n[Time].[Day].DateMembersBetween('Jun 13 2022', 'Jun 26 2022')\n)","format_string":""},{"name":"DW Sprint 8","dimension":"Time","formula":"Aggregate(\n[Time].[Day].DateMembersBetween('May 30 2022', 'Jun 12 2022')\n)","format_string":""},{"name":"Story Points to Dev Complete","dimension":"Measures","formula":"([Measures].[Story Points (Jira) added], [Transition].[In Progress =\u003e Dev Complete])","format_string":"#,##0"},{"name":"Story Points to Lead Review","dimension":"Measures","formula":"([Measures].[Story Points (Jira) added], [Transition].[To Do =\u003e Ready for QA]) + ([Measures].[Story Points (Jira) added], [Transition].[In Progress =\u003e Ready for QA]) + ([Measures].[Story Points (Jira) added], [Transition].[In Progress =\u003e Ready for Lead Review])","format_string":"#,##0"},{"name":"Story Points to Closed","dimension":"Measures","formula":"([Measures].[Story Points (Jira) added], [Transition].[To Do =\u003e Closed], [Resolution].[Done]) + ([Measures].[Story Points (Jira) added], [Transition].[To Do =\u003e Closed], [Resolution].[Fixed]) + ([Measures].[Story Points (Jira) added], [Transition].[In Progress =\u003e Closed], [Resolution].[Done]) + ([Measures].[Story Points (Jira) added], [Transition].[In Progress =\u003e Closed], [Resolution].[Fixed])","format_string":"#,##0"}]
}

[zane.baranovska]

Thanks, @rbelmont, for the report definition.

The sprint scope measures do not work for your use case because the Sprint dimension is not used in the report. I see you use the Time dimension on report rows for representing sprints on the timeline.

In this case, you might want to proceed with measures representing issue change history, like “Issue history”, “Story points history”, “Transition to status first date” and others (Import issue change history).

For example, to sum up, the story points of issues that moved to Dev Complete in the selected period, you might use an expression like this:

Sum(
  Filter(
    -- filter issues having a particular transition during an active sprint
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      --trnasition date ignoring the assignee at the time
      DateWithoutTime((
        [Measures].[Transition to status first date],
        [Transition Status].[Dev Complete],
        [Assignee].CurrentHierarchy.DefaultMember )),
      [Time].CurrentHierarchyMember )
  ), --sum up story points 
  ([Measures].[Story Points history],
  [Assignee].CurrentHierarchy.DefaultMember,
  [Transition Author].CurrentHierarchy.DefaultMember)
)

If you woudl like to analyze data by a user who moved issues to the specific status (Dev Complete), use the dimension “Transition Author” on report rows.

[rbelmont]

Thanks @zane.baranovska. I’ve found if I use the Sprint dimension I run into a different issue. For example:

1. Ticket A is in Sprint 2. During Sprint 2, Ticket A is moved from In Progress => Dev Complete.
2. Ticket A is rolled over to Sprint 3 when Sprint 2 is closed (i.e. Ticket A wasn’t closed out).

Expected Result: The story points for Ticket A appear in the Sprint 2 velocity total
Actual Result: The story points for TIcket A appear in the Sprint 3 velocity total (even though the transition being measured occurred during Sprint 2)

Here is the report I’m trying to build, I’d love for your input on the best way to build it (I’ve already built this in Excel, trying to replicate in EazyBI where our other reports live):

Overview

1. Our team has 2 week sprints. Due to resourcing and the nature of the work, tickets are rarely closed out in the sprint in which dev work was completed (i.e., the tickets are not resolved until 1+ sprints later and tend to roll between sprints. This makes traditional velocity metrics difficult to use.

2. We have a flexible workflow where some transitions are conditional/optional. That means that a ticket that is “completed” in the sprint (from the standpoint of measuring velocity) could follow one of these paths:

• In Progress => Dev Complete
• In Progress => Ready for Lead Review/Ready for QA
• To Do/In Progress => Closed

3. Based on the above, I’ve created (and am trying to replicate) a report that:

• Calculates the number of points moved from In Progress ==> Dev Complete in each sprint, based on the start/end day of the sprint and the Dev Complete Date of each ticket (custom field)
• Calculates the number of points moved from In Progress ==> Ready for Lead Review/Ready for QA in each sprint (using the Review Submission Date and the absence of a Dev Complete Date)
• Calculates the number of points moved from To Do/In Progress ==> Closed in each sprint (using the Resolved date and the absence of Dev Complete Date and Review Submission Date)
• Sums the three totals to capture “sprint velocity”
• Calculates a rolling 3-sprint average velocity
• Supports filtering by Primary Developer (custom field) to see individual velocity (note - it’s not always the developer moving their own tickets, so looking at Transition Author has limited utility here)
• Only recognizes story points for each ticket once, regardless of how many times it’s transitioned between these statuses

Any suggestions? Is there any way I could schedule time to work with someone on this?