All eazyBI for Jira eazyBI for Confluence Private eazyBI
All eazyBI for Jira eazyBI for Confluence Private eazyBI

Kanban project overview

On this page:

Overview

This report shows the information on several Kanban KPIs. Count and age of issues for waiting for development that is currently in backlog. Shows how many issues are in active working cycle represented by Work in Progress, and the average length of this cycle. It shows a throughput overview as well.


How to build it

The report uses Project dimensions in Rows and on Pages. The report uses several custom measures representing KPIs. Custom measures are defined as report specific, if they are valuable, define them as user-defined and share them with several reports. Feel free to add any other dimension that will represent the scope you would like to analyze in the report. For example, Fix Version, Epic Link, any custom dimension, like Team etc.

Current backlog issues

This measure counts issues in a Backlog for Today. Statuses To Do and Selected for Dev define Backlog in this example.

This measure uses a Sum over a set of several Statuses. Use curly brackets to define a set and specify all statuses representing a backlog. The example formula uses two statuses - To Do and Selected for Dev. The Sum function numeric argument uses a tuple with measure Issues created and Time default member. It will count issues based on the specified set of statuses and any selection in the report using current values in issues. The formula will ignore Time selection in the report and show values for Today.

  Sum(
  -- list the backlog statuses
    {[Status].[To Do],
    [Status].[Selected for dev]},
    -- count valid issues no matter when they were created
    ([Measures].[Issues created],
     [Time].CurrentHierarchy.DefaultMember)
  )


Current backlog average age

This measure calculates the age of issues in the Backlog for Today. Statuses To Do and Selected for Dev define Backlog in this example.

Age consists of two parts:

  • time in status since the last status update 
  • the previous time in backlog if the issue has been moved back/forth from the backlog. In this case, the formula uses a Sum over a set of Transition statuses and counts days in transition status in a tuple with a time default member. Thus ignoring when the issue was in those statuses before.

This measure uses Avg over a set of filtered Issues. The formula access all issues with function Descendants and filters issues using the issue status property. The example formula uses two statuses - To Do and Selected for Dev. Those statuses are used to filter issues and in calculation with the Transition status dimension. 

Please update and use the same statuses in all places in this and in the previous formulas to get consistent results.

Avg(Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  -- filter issues currently in backlog statuses: list the backlog statuses
  [Measures].[Issue status] MATCHES "To Do|Selected for dev"),
  CASE WHEN 
  -- filter valid issues, no matter when the issue was created
  ([Measures].[Issues created],
   [Time].CurrentHierarchy.DefaultMember) > 0
  THEN
  -- days since the last status change
  DateDiffDays(
    [Measures].[Issue status updated date],
    Now())
  +
  -- add time for cases when issue is moved to/from backlog
  Sum(
  -- list the backlog statuses - same as in MATCHES
    {[Transition Status].[To Do],
    [Transition Status].[Selected for dev]},
    -- total time spent in previous time issue was in the status 
    ([Measures].[Days in transition status],
     [Time].CurrentHierarchy.DefaultMember)
  )
  END
)


Work in progress

The formula shows how many issues in the active working cycle are at the end of the selected period. The time dimension in the report will change the results of the formula. Any status of the category In Progress defines the active working cycle.

The formula uses a tuple with measure Issues history and Transition status member. The current formula uses the Transition status category In Progress. If needed create a new calculated member in the Transition status dimension and list a subset of statuses representing work in progress. 

The formula represents the historical count of issues in progress statuses over time. Use time selection in the report to show how many issues were in progress at any period back in time.

(
  [Measures].[Issues history],
  -- add the transition status member representing your cycle
  [Transition Status.Category].[In Progress]
)


Average cycle time

The formula will calculate the average total time spent in the active working cycle for resolved issues in the selected period. The time dimension in the report will change the results of the formula. Any status of the category In Progress defines the active working cycle.

The formula uses a tuple with measure Average days in cycle and Transition status member. The current formula uses the Transition status category In Progress. If needed address a different Transition status member. Please use the same as for the formula Work in progress to get consistent results. Average days in cycle is a custom calculation from the example report Average lead and cycle time in our demo account.

(
  [Measures].[Average days in cycle],
  -- add the transition status member representing your cycle
  [Transition Status.Category].[In Progress]
)


Average cycle time overview

The formula shows Average cycle time as an inline chart for several periods.

The formula uses the function SparklineData to show an inline chart representing the Average cycle time over time using the past several months. Function SparklineData requires special formatting Sparkline for the measure to represent data as an inline chart. The formula is similar to Average throughout overview the difference is what measure is used to show values on Sparkline.

SparklineData(
  -- up to 12 month of the selections till today
    Tail(
    Filter(
      DescendantsSet([Time].CurrentMember,[Time].[Month]),
     -- only periods till today
     DateCompare([Time].CurrentHierarchyMember.StartDate,now()) < 0),
     -- parameter for tail - last 12 members
     12) ,
  -- Numeric_Expression ----- Change measure here if needed
  [Measures].[Average cycle time]
)


Average throughput

The formula will calculate an average count of resolved issues per day for a selected period. The time dimension in the report will change the results of the formula.

-- count average resolved issues for any weekday for a selected time period
Avg(Filter(
  -- access all days for a selected time period   
  DescendantsSet([Time].CurrentHierarchyMember,[Time].CurrentHierarchy.Levels("Day")),
  -- exclude weekend
  NOT [Time].CurrentHierarchyMember.Get('Week day name') MATCHES "Saturday|Sunday"
  AND
  -- count till yesterday only to include full completed days
  DateCompare([Time].CurrentHierarchyMember.StartDate, "Yesterday") <=0
  ),
  -- count resolved issues or 0 if there are no resolved issues in a weekday
  CoalesceEmpty([Measures].[Issues resolved] ,0)
)


Average throughout overview

The formula shows Average throughput as an inline chart for several periods. 

The formula uses the function SparklineData to show an inline chart representing the Average throughput over time using the past several months. Function SparklineData requires special formatting Sparkline for the measure to represent data as an inline chart. The formula is similar to Average cycle time overview the difference is what measure is used to show values on Sparkline.

SparklineData(
  -- up to 12 month of the selections till today
    Tail(
    Filter(
      DescendantsSet([Time].CurrentMember,[Time].[Month]),
     -- only periods till today
     DateCompare([Time].CurrentHierarchyMember.StartDate,now()) < 0),
     -- parameter for tail - last 12 members
     12) ,
  -- Numeric_Expression ----- Change measure here if needed
  [Measures].[Average throughput]
)


All calculated measures are added to the report. The report uses Gauge chart and represents only values. Measures Work in progress, Average cycle time, and Average cycle time overview use gauge limits to set the color blue. Measures Average throughput, and Average throughput overview use gauge limits to set the color green.

See also