Rolling median of resolution time

Questions & Answers
1

I would like to create a report which shows on a daily basis two rolling values for the past 30 days:

  1. Count / sum of closed issues.
  2. Median resolution time of tickets from 1. Note: resolution time in my case is meant as time til status “closed”.

Number one is easy to get with

Sum(
  LastPeriods(30, [Time].CurrentHierarchyMember),
  [Measures].[Issues closed]
)

but number 2 is giving me headaches. I tried to modify a similiar measure I already have (median of resolution time for given time selection) but I am constantly failing to do so:

Median(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod([Issue].CurrentHierarchyMember.get('Closed at'),
      [Time].CurrentHierarchyMember) AND
      ([Measures].[Issues created],
       [Time].CurrentHierarchy.DefaultMember) > 0
  ),
 DateDiffWorkdays(
        [Issue].CurrentHierarchyMember.get('Created at'),
        [Issue].CurrentHierarchyMember.get('Closed at'))
)

I feel like I am a too frequent user to fully handle the details of filters, sets and expressions to implement what I need since I forget the details in between. Any hint or help is much appreciated.

2

Hi @Maiko

You’re on the right path!

Please try out this formula:

Median(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateBetween(
      [Issue].CurrentHierarchyMember.Get('Closed at'),
      DateAddDays([Time].CurrentHierarchyMember.StartDate,-30),
      [Time].CurrentHierarchyMember.NextStartDate
    ) 
    AND
    (
      [Measures].[Issues closed],
      [Time].CurrentHierarchy.DefaultMember
    ) > 0
  ),
  DateDiffWorkdays(
    [Issue].CurrentHierarchyMember.get('Created at'),
    [Issue].CurrentHierarchyMember.get('Closed at')
  )
)

You can read more about the functions used in this formula here: MDX function reference

​Best regards,
​Nauris

3

Hello @nauris.malitis

Thank you for your input. I looked into your proposal and made a test with my data. There still seems to be something off, because for a given date (as mentioned above I build the report on daily basis) the number of issues shown when drilling through is unequal between the two KPIs. The number of issues drilling through median KPI is mostly lower than the sum of issues KPI and sometimes equal. I suspect that there is an issue with the date boundaries. For context, the time filter I use is defined as “30 days ago and today”.

4

Hi @Maiko

The formula suggested works best with Time members in the report Rows (especially days).
For each day, it finds the Median issue of the last 30 days until that relative day.

Could you please explain how you wish this would work for given dates if the time filter is “30 days ago and today”? In this case the given day does not play a role or does it?

​Best regards,
​Nauris

5

Sorry for the late reply, I did not receive a notification.

Time members (here: days) are in rows, I think the “basic setup” fits. For both KPIs, I would like to collect closed issues from the past 30 days and for #1 sum them up and for #2 calculate their median. My report period is 30 days as well, therefore the filter is “30 days ago and today". Does that answer your question?

I assume that drilling through both KPIs will show the same issues which is not the case.

6

Any takers? I can share the report’s denition as well.

7

Hi @Maiko

Sorry for the delay!

A formula like this will count the number of closed issues for the last 30 days from each of the day members in the report:

Count(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateBetween(
      [Issue].CurrentHierarchyMember.Get('Closed at'),
      DateAddDays([Time].CurrentHierarchyMember.StartDate,-30),
      [Time].CurrentHierarchyMember.NextStartDate
    ) 
    AND
    (
      [Measures].[Issues closed],
      [Time].CurrentHierarchy.DefaultMember
    ) > 0
  )
)

​Let me know if this fits your use case or if you have any additional questions on this!
​Best regards,
​Nauris