Issue Average Ageing for few Statuses

Questions & Answers
1

Hi Team,

We have created the following custom measure

Measure Name: Actual Oustanding

Aggregate(
Except(
[Status].[Status].Members,
{
[Status].[Permanently Closed],
[Status].[Pending Client Confirmation],
[Status].[Assigned to SME],
[Status].[ConfirmWithCloud],
[Status].[Account Management]
}),
[Measures].[Issues created]
)

For these issues, we want to ticket average aging (difference between Now()- Issue created), when we select “Total Row” then it should give the average aging of all the issues.

Could you please help us on this.

Regards
Kasi

2

Hi @kasi.maddula ,
The best would be to create the calculated member in your Status dimension, so you could filter the report by other measures also (not only “Issues created”):

Aggregate(
  Except(
    [Status].[Status].Members,
    {
    [Status].[Permanently Closed],
    [Status].[Pending Client Confirmation],
    [Status].[Assigned to SME],
    [Status].[ConfirmWithCloud],
    [Status].[Account Management]
  })
)

Then you can use predefined measure “Average age days” together with this Status dimension member. Here is an example report from eazyBI Demo dashboard: Average age of unresolved issues by issue type and priority - Issues - Jira Demo - eazyBI

Kind regards,
Gerda // support@eazyBI.com

1 Like
3

We had a similar request where we wanted to see the amount of time an issue spent “Blocked” per month. We worked with EasyBI to get the following solution:

  1. Created a custom member [Transition Status].[Blocked Statuses] consisting of the “Blocked” Statuses:
    Aggregate({
    [Transition Status].[Active],
    [Transition Status].[Awaiting Information],
    [Transition Status].[Owned],
    [Transition Status].[Awaiting Dependency],
    [Transition Status].[Awaiting FI Assistance],
    [Transition Status].[Blocked]
    })

  2. Created a custom measure [Measures].[Blocked Days] for reporting up to the report date for time in that status:

– Provided by EasyBI Support
Case WHEN [Measures].[Open Issues (not Closed)] > 0
AND
DateAfterPeriodEnd(
now(), [Time].CurrentHierarchyMember.PrevMember
)
THEN
Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Created at’),
[Time].CurrentHierarchyMember
)
AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Closed at’),
[Time].CurrentHierarchyMember
)
),
CASE WHEN
(
[Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember
)>0
THEN
CASE
–current period
WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
THEN
([Measures].[Days in transition status],[Transition Status].[Blocked Statuses],[Time].CurrentHierarchy.DefaultMember)
+
CASE WHEN
[Measures].[Issue status] matches “Active|Awaiting Information|Owned|Awaiting Dependency|Awaiting FI Assistance|Blocked” --again blocked statuses to match for current status
THEN
DateDiffDays(
[Measures].[Issue status updated date],
Now()
)
END
–current period ends

   --previous periods
    ELSE 
    Sum(--sum starts here
        { PreviousPeriods([Time].CurrentHierarchyMember),
         [Time].CurrentHierarchyMember},
        ([Measures].[Days in transition status],[Transition Status].[Blocked Statuses])
        ) --sum ends here
        
        +
        Nonzero(
        iif(
        ( --if blocked status present at the end of displayed month
          [Measures].[Issues history],
          [Transition Status].[Blocked Statuses]
        )>0 
        AND
                    ( --if blocked status present at the end of displayed month
          [Measures].[Open Issues (not Closed)]
        )>0 
        
        , 
        Datediffdays(
            TimeStampToDate(MAX(
                {PreviousPeriods([Time].CurrentHierarchyMember),
                 [Time].CurrentHierarchyMember},
                 DateToTimeStamp((
                 [Measures].[Transition to status last date],
                 [Transition Status].[Blocked Statuses]
                 ))
                ))
            --last date when blocked status set
          ,
          [Time].CurrentHierarchyMember.NextStartDate --first date of next month
        ),0)  
        )
    
    --previous period ends
  END

END
)
END

  1. We already had a measure for Open issues [Measures].[Open Issues (not Closed)] (Differed from EasyBI measure as we used closed rather than resolved dates for calculation:
    –Copy of out of the box open issues using closed data rather
    –than resolved data
    CASE WHEN [Issue].CurrentMember.Level.Name <> ‘Issue’ THEN
    Cache(
    NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
    Cache([Measures].[Issues created]
    - [Measures].[Issues closed])
    ))

    • [Measures].[Issues created]
    • [Measures].[Issues closed]
      )
      WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
      THEN NonZero([Measures].[Issues due])
      ELSE
      – optimized formula for drill through Issue
      NonZero(IIF(
      DateBeforePeriodEnd(
      [Issue].CurrentMember.get(‘Created at’),
      [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
      [Issue].CurrentMember.get(‘Closed at’),
      [Time].CurrentHierarchyMember),
      ([Time].CurrentHierarchy.DefaultMember,
      [Measures].[Issues created]),
      0
      ))
      END

  2. used the 2 custom measures to Create a custom measure for the Average:

[Measures].[Blocked Days]/[Measures].[Open Issues (not Closed)]