Using Issue Closed on Date in Jira Structure Formula

[Zigi Fibert]

I want to calculate the number of story points closed between 2 dates and used the number of team members to calculate velocity.

Something like (Sum the number of story points of issues closed between day-X and day Y and divide it by Z (number of people in the team).

Any help on how to achieve this will be welcome.

Regards

Zigi Fibert

[Alessandro C_ _ALM Works_ · Accepted solution]

Hello Zigi, Alessandro from ALM Works here.

You can actually easily calculate what you've been mentioning by building a new structure and getting into the board, issues from a defined period by using JQL.

From there, you can group the issues by assignee and use some formula columns to the math.

Note that this is a mere example I quickly built, Much likely, there are different approaches you can take with Structure to achieve the same result. That said, here's what you can do:

1. Create a new Structure and add some issues from a Period 

   Project = STR AND (status changed to Done during ("2020/11/09", "2020/11/12") )

   I limited my query to a project in this example, but you can do it with multiple projects if you'd like to.
   You can modify the timeframe in the "During" parenthesis as you need.

2. Group by "Assignee", then group by "Project"
3. Click on the "+" icon at the rightmost corner of the Columns row and look for "Σ Story Points".
   This is a formula bundled in Structure that automatically sums the Story points for you. (Make sure that at the columns properties, the 'Sum over sub-items' is marked)

At this point you should already have a structure showing the issues resolved on the specified period and how many Story Points have been accumulated per assignee.

The next step is to figure out, using structure, how many Assignees the structure you've built have:

1.  Add a new column, this time, look for "Formula...".
2. 'Name' it as you please and on the 'Formula' field write 'query' and then save (link located at the bottom right corner of the formula field) to create a new variable.
3. Click on the variable marked with the red indicator, right below the 'Formula' field.
4. Select "Query match" on the drop-down that will appear. This will bring up more options.
5. At the "Query Type" drop-down select S-JQL. Type the following query:

   child of root

   (This query will be used to dinamically count how many assignees appeared in your structure using the Structured JQL feature)
6. At the "Options" section, once again, make sure you check the 'Sum over sub-items' option.
7. Change the "Format" to 'Number' and click away from the Column panel to see the changes

Finally, create a new "Formula ..." field to calculate the average between assignee by story points:

1. Look for "Formula..." once again (inside '+" at the rightmost corner of the Columns row)
2. 'Name' the formula as you like. ('Velocity', as you mentioned, for example).
3. Add the following formula and save afterwards:

   IF(Type = Project; Story_Points + Resources; "")

   (If any of the fields appear in red at the Variables list, click on them and select the corresponding column)
4. Click away from the column properties to see the result.
   (As this time the calculation is intended to be done at Project level, there's no need to mark the 'Sum over sub-items')

Finally, you should be able to see the calculation you've mentioned in this new column.

(Note that, if you would like to, you can remove the columns created previously, as Structure stores the calculations used on the 'Velocity' column)

I hope this helps!

Best regards,
Alessandro C.

[Bill Sheboy]

Hi @Zigi Fibert 

What problem are you trying to solve?

What you are describing is not the velocity of the team; the velocity would be the amount of work the team finished during a sprint, not one person on the team.  There are existing reports built into Jira to measure velocity over time.

Best regards,
Bill

[Zigi Fibert]

Thanks, @Bill Sheboy .

I use Velocity Per Person Month to estimate the capacity of the team for planning purposes.  I need to refresh this estimate on an ongoing basis.

Regards

Zigi

[Bill Sheboy]

Thanks for explaining, @Zigi Fibert 

Unless you have a lot of churn in the people who are on the team or in the lengths of the sprints, why not just use sprint velocity, convert to "months" and adjust it for capacity?  (Ignoring old data and months with many holidays.)

For example, if your team's sprints are 2 weeks long, and:

• Sprint 1 velocity is 15
• Sprint 2 velocity is 21
• Sprint 3 velocity is 10 (4 holidays this sprint, so we ignore it)
• Sprint 4 velocity is 18

Your average velocity is (15 + 21 + 18) / 3 = 18

Your average monthly velocity (2 sprints) is 36

For capacity planning impacts, ask people to identify their out of office/vacation/holiday time far enough in advance for whatever planning timeframe the team needs, at least before each sprint's planning.

[Zigi Fibert]

Thanks, Bill.

We not doing sprints but a continuous development over a period of a few months.  This is why I want to measure story points closed within a time period.

We also have up to a 20% increase in resources towards the end of the delivery estimate.

I can handle that once I know how to calculate the SP closed within a certain period of time.

Regards

Zigi

[Bill Sheboy]

Thanks, @Zigi Fibert   That helps clarify quite a bit!

Okay, your team is using continuous flow practices (Kanban) rather than iterations with sprints (Scrum).  Often teams using Kanban measure in terms of Cycle Time and Throughput (the equivalent of Velocity for Scrum) to help improve and to forecast.  So, how do you get that information?

The built-in reports can help, but are not (in my opinion) accurate representations of Cycle Time or Throughput for teams.  Two alternatives are marketplace add-ons for dashboards or export/analyze in a spreadsheet.  With add-ons it may be unclear what is being measured; with a spreadsheet you know exactly what is happening (because you built it).

Regarding the increase in team capacity by 20%, you do not need a by-person count to forecast for that.  Instead just multiple your monthly throughput by 120%.  Of note, adding more people to a project rarely increases immediately due to on-boarding.  More often, throughput goes down before it goes up, so consider that for your planning.