Average workdays spent by Time filter

Hi EazyBi Team,

I got stuck at an issue, Can you please check the below at your convenience and do the needful.

Attached the image , The intention is to have data on the average workdays spent on the issue by time filter.
I have made ‘time’ as common filter and filtered issues by 2021 but I see issues coming even from 2018 ( May be one of the transition state for the issue is in 2021) but I would like to filter the resultset only to have the issues created after the selected ‘Time’ filter.

Please let me know if the above makes sense.

Hi,

The Time filter relates to every measure by a different date (issue creation date, resolution date, due date, worklog date, transition date, etc.).
Average workdays in transition status is related to the date when the issue moved out of the particular status, not to issue creation date.

To override this standard behavior, you may want to create a new calculated measure (in Measures).
As a base for this measure use measure “Average days in cycle” from the demo report Average lead and cycle time - Issues - Jira Demo - eazyBI

You would modify this calculation: use “Workdays in transition status” instead of “Days in transition status” in the calculation formula and count issues by their creation date, regardless of if they are resolved or not.

The final calculation may look the following:

-- use in a report or in another formula with Transition status dimension
IIF(
  [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember,
    -- with time default memeber total workdays in status / distinct count of issues moving from the status for all issues
    [Measures].[Workdays in transition status]
    /
    [Measures].[Transitions from status issues count],
  -- calculation for a selected period/timeline requires issue level calculation. 
  NonZero(AVG(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      -- filter issues created in period
      DateInPeriod(
        [Measures].[Issue creation date],
        [Time].CurrentHierarchyMember
    )),
    CASE WHEN 
     -- filter created in period issues by any dimension, except transition status
      ([Measures].[Issues created], [Transition Status].CurrentHierarchy.DefaultMember) > 0
    THEN
    -- count total time spent for this issue in a particular status - use Transition status in report 
      NonZero(
        DefaultContext((
         [Measures].[Days in transition status],
         [Issue].CurrentMember,
         [Transition Status].CurrentHierarchyMember))
      )
    END
  ))
)

Use it instead of default Average workdays in transition status.
Note, this calculation iterates through issues, therefore, can be quite slow.
Use it in a simple report which is created taking into account best report creation practices: Best practices for report creation - eazyBI for Confluence

Best,
Ilze / support@eazybi.com