Weekly Trend Measure of Average Age of Open Tickets Including Historical Data

Questions & Answers
1

Hello, I need to create a new measure that shows the weekly trend of the average age of the still open tickets. In my organization, we consider the age of the issue as the working days between opening and closing.

I created the measure “Open Issue”, through which I can see how many tickets were left open at the end of the week

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 created]
  - [Measures].[Issues closed]
)
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

I then created a second measure “Workdays Aging” which associates each issue with the working days (if closed up to the closing date, otherwise up to now).

CASE WHEN isEmpty([Measures].[Issues closed])
THEN
  DateDiffWorkdays(
    [Measures].[Issue created date],
    Now()
  )
ELSE
  DateDiffWorkdays(
    [Measures].[Issue created date],
    [Measures].[Issue closed date]
  )
END

This measure works well only if I don’t need to view historical data. When I set the weekly view, the aging value always remains the same for all the weeks set. The value also remains the same for the weeks following the closing one.

I kindly ask for your help to update this measure or create another one for historical views. I really don’t know how to do it. The aging should increase weekly for issues that remain open and stop when the issue is closed.

Thanks in advance

2

Hi @DamianoDB,

The calculation to get the age of any issue is correct. To get the average of all open issues in the period,you should create another calculated measure that would iterate through individual issues, and for each check, it was open in the period.

Avg(
  --set of issues
  Filter(
    DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
    --check if issue was open at beginning of period
    DateBeforePeriodEnd(
      [Issue].CurrentMember.get('Created at'),
      [Time].CurrentHierarchyMember) AND
    --issue closed in this or some future period (includes also not closed issues)
    (
      IsEmpty([Issue].CurrentMember.get('Closed at')) OR
      DateInPeriod(
        [Issue].CurrentMember.get('Closed at'),
        [Time].CurrentHierarchyMember) OR
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Closed at'),
        [Time].CurrentHierarchyMember)
    )
  ),
  --for each issue calculate age
  CASE WHEN --issue match report context
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember) > 0
  THEN 
    DateDiffWorkdays(
      [Measures].[Issue created date],
      --till closed date or today if issue is still open
      CoalesceEmpty([Measures].[Issue closed date],"today")
    )
  END
)

More details on calculated measures and mentioned functions are described in the documentation:

Best,
Zane / support@eazyBI.com