Average assignment time per sections

Questions & Answers
1

Ok, so I’m trying to build something similar to what I saw in Kanban project overview and I was able to get the following…

image
image862×497 23.2 KB

Which is not bad… However, I use scrum (without sprints)…

But some of the “To Do” are in Sprints… so no longer in the Backlog… How can I improve my calculated measure to show the actual number of issues in the backlog…

Current backlog number of issues calculated measure is the following:

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

Current backlog average age (days) calculated measure is the following:

Avg(Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  -- filter issues currently in backlog statuses: list the backlog statuses
  [Measures].[Issue status] MATCHES "To Do|Draft"),
  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].[Draft]},
    -- total time spent in previous time issue was in the status 
    ([Measures].[Days in transition status],
     [Time].CurrentHierarchy.DefaultMember)
  )
  END
)

Thanks in advance for your help!

2

Hi @HRivest,

You can exclude issues that are assigned to a Sprint by looking for issues in the “(no board)” member of the Sprint dimension. The “Current backlog number of issues” formula could look similar to the one below:

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

Include the “(no board)” member in the CASE WHEN condition of the average calculation:

...
CASE WHEN 
  -- filter valid issues, no matter when the issue was created
  ([Measures].[Issues created],
   [Sprint].[(no board)],
   [Time].CurrentHierarchy.DefaultMember) > 0
...

Best,
Roberts // support@eazybi.com

1 Like