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

1 What is “Velocity by months” metric?
2 Creating a metric
3 Creating a Custom Selection
4 Retrieving the required data and defining month for every row
5 Data aggregation and grouping
6 Full code recap

What is “Velocity by months” metric?

Velocity shows an absolute amount of value (measured in story point or items) delivered within a months. It helps to determine an amount of work a current project team can process within a particular period

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

Retrieving the required data and defining month for every row

select 
      key as item, 
	  story_points as story_point, 
  	  done_date as finish_dt,
      date_trunc('month', done_date) as month_start          
 from ticket
where lower(status) in ('done', 'closed')
  and lower(type) not like 'sub%'               --sub-items (i.e. sub-tasks, sub-bugs in JIRA) are not included into the calculation;
  and done_date < date_trunc('month', now())    --only completed week/months are shown in the chart, no future ones;

To define the month for every value of “done_date“ we need to truncate dates to month. Items completed during the same month but different days will have the same “month_start“ for further grouping. Also it’s required to take into account only completed tickets, to exclude sub-items from the result and to exclude data for current month

Example of the output:

Data aggregation and grouping

To get required result we need to extract Year and name of Month from “month_start“, summarize story points, count tickets and group aggregated data by “month_start“

Full code recap

with 

tickets_and_periods as 
(
select 
      key as item, 
	  story_points as story_point, 
  	  done_date as finish_dt,
      date_trunc('month', done_date) as month_start       
 from ticket
where lower(status) in ('done', 'closed')
  and lower(type) not like 'sub%'               --sub-items (i.e. sub-tasks, sub-bugs in JIRA) are not included into the calculation;
  and done_date < date_trunc('month', now())    --only completed week/months are shown in the chart, no future ones;
)

select  
      to_char(tickets_and_periods.month_start, 'yyyy Mon')
      as month,
      count(tickets_and_periods.item) as items,
      sum(tickets_and_periods.story_point) as sp
 from tickets_and_periods
group by 
         tickets_and_periods.month_start
order by tickets_and_periods.month_start    
;

Example of the output: