Help setting up a Filter for 'Resolved' Jira Issues in a period of time

Questions & Answers
1

Hi everyone,

I’m trying to get the average workdays that Jira issues spend in each ‘status’, for that i’ve created some measures like this one:

Aggregate(
 {
 [Transition Status].[Analyst],
 [Transition Status].[Developer],
 [Transition Status].[in QA]
 },
 [Measures].[workdays/count nº status]
)

Where [Workdays/count nº status] is:

[measures].[Workdays in transition status]/([Measures].[Count nº status]))

And [count nº status] is:

Case When [measures].[Transitions from status]>0 then
sum([measures].[Transitions from status])
End

Columns: Measures like the first one

Rows: Time

The information i get to this point is correct,

the problem is that i need to show only the data of the Jira issues that have the status switched to ‘resolved’ during the previous month (last 30 days would be OK if filtering by previous month is not possible)

So, if a Jira issue has been open since March and is Resolved in June, i need to get the information from March to June. If i just filter by month it only shows the data of the statuses that have been active in June, losing the statuses active during the previous months, Example:

  • In status X from Month to April ->Won’t show

  • In status Y from Month to June -> Will show all the data from Month to June

To create that filter i thought about using the measure: [Transition to status last date] (i don’t know if it’s a standard one so here’s the code:

TimestampToDate(
[Measures].[Transition to status last timestamp]
)
But i don’t know how to create the correct calculation for this filter.

How could I get the desired results?

Thanks in advance

2

Hi,

In this case, you could try creating a new calculated member in “Measures” dimension using the following code:

--annotations.group=Averages
IIF(
  [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember,

   Aggregate({
       [Transition status].[Analyst],
       [Transition status].[Developer],
       [Transition status].[in QA]},
     ([Measures].[Workdays in transition status],
      [Time].CurrentHierarchy.DefaultMember))
   /
   [Measures].[Issues resolved] ,
  -- per period calculation on issue level
  NonZero(AVG(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember
      )
      AND
      [Measures].[Issues resolved] > 0),
-- aggregattin list of cycle transtion statuses
     Aggregate({
       [Transition status].[Analyst],
       [Transition status].[Developer],
       [Transition status].[in QA]},
     ([Measures].[Workdays in transition status],
      [Time].CurrentHierarchy.DefaultMember))
    )
  )
)

Please reach out to support@eazybi.com with more details on the use-case if that didn’t help as you expected.

Martins / eazyBI support