[EazyBI] - Help setting up a Filter for 'Resolved' Jira Issues in a period of time

[Enric Herrerias Piquer (External)]

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:

**it's a measure**

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

[Lauma Cīrule]

Hi!

It is possible to use Tuples and Default dimension members to first get issues resolved in period ignoring the Transition statuses; further get Average days in transition status ignoring the Time. 

Please try the following formula: it first finds all issues that have resolved date in the period and further sums Average days in all transition statues ignoring when the issue transitioned away from the status. 

Sum(
 Filter(
 Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
 IIF([Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember,
   1,
   DateInPeriod(
     [Issue].CurrentHierarchyMember.Get('Resolved at'),
     [Time].CurrentHierarchyMember) AND
   ([Measures].[Issues resolved], 
    [Transition Status].DefaultMember) > 0)),
 ([Measures].[Average workdays in transition status], 
  [Time].CurrentHierarchy.DefaultMember)
)

This measure can be used together with Aggregated group of Transition statues.

Lauma / support@eazybi.com

[Hug Collellmir Almirall (External]

I have a similar requirement.

Please does anyone have any solution?

Regards,