This article describes what “Average velocity by months” metric is and how it helps and works

What is “Average velocity by months” metric?

Average Velocity shows an amount of value (in story points or items) delivered within 1 - 3 - 6 - 12 months. Average Velocity helps to compare the productivity of a team on a long-term versus a short-term time interval - it reveals if an overall performance improves or degrades

Creating a metric

See Custom Metric development for details

Creating a Custom Selection

See Custom Metric development to see how to get to a Custom Selection mode.

Further will be the explanation of the code you should put in the “PerfQL” field

Including/excluding sub-items

First of all we need to decide if we want to include sub-items into the calculation or not. To include sub-items we need to change number “1” in the first query to any other number, for example 0 or 2. If there is a need to exclude sub-items - skip this point (the query must look as shown below)

include_sub_items as (
    
select 1 -- 1 - exclude sub-items, any other number - include sub-items
    as y_n
)

Generating last 12 months

To generate first day of every month of last 12 months we use “generate_series()“ function where the beginning and the end of required period of time are defined. To find the day of the beginning we need to subtract interval of 12 months from the first day of the current month. To find the end of the period we need to subtract 1 day from the first day of the current month to exclude it from the result. The step “1 month“ divides this period of time into 12 months. To find the end of every month we need to add 1 month to the beginning of it to get the first day of the next month, then subtract 1 day to get the last day of the month and finally add 23 hours, 59 minutes and 59 seconds to get last day of month 23:59:59

months as (
    select a.start_of_the_month, 
           a.start_of_the_month 
           + interval '1 month' 
           - interval '1 day' 
           + interval '23 hour 59 minute 59 second' as end_of_the_month 
      from (
            select * from generate_series(  
  
                                date_trunc('month', now())
                                - interval '12 month',

                                date_trunc('month', now()) 
                                - interval '1 day',
                                                          	
                             	'1 month') as start_of_the_month
                                
     ) a
)

Example of the output:

Retrieving and filtering required data

PerfQL-statement below retrieves data according to calculation requirements

spsanditems as (
select
        done_date    as finish_dt, 
        key          as items, 
        story_points as story_points       
  from  ticket
 where  lower(status) in ('done', 'closed', 'resolved')
   and  ((select y_n from include_sub_items) <> 1 OR lower(type) not like 'sub%')                
)

Example of the output:

Joining of generated months and filtered data

“done_date“ must be between the start and the end of its month for proper joining months and filtered data. The result set of this query is sorted descending by “start_of_the_month“ for proper calculation from last 1 months to last 12 months

Example of the output:

Full code recap

Finally we need to combine four similar queries to aggregate data within last 1 - 3 - 6 - 12 months

Example of the output: