Average resolution time based on custom field date

Questions & Answers
1

Hi team,

I want to calculate average resolution time based on custom date field instead of created date… I am trying to find out in measures but not able to figure it out how to use that measure from custom date field to resolution… average time spent on a status.
Thanks in advance

2

Hi @keyan

First, make sure you selected both checkboxes (measure and property) for your date picker custom field from the eazyBI import options page and import data to create necessary measures for the calculations

Then you could create a new calculated measure with “decimal” output format using this formula:

CASE 
  WHEN
   NOT [Project].CurrentHierarchyMember is [Project].CurrentHierarchy.DefaultMember
   AND
   [Issue].CurrentHierarchyMember is [Issue].CurrentHierarchy.DefaultMember
  THEN
  --optimized iteration
  Avg(
    Filter(
       Generate(
      --set of relevant projects
        Filter(
          [Issue].[Project].Members,
          DefaultContext(([Measures].[Issues created],[Issue].CurrenthierarchyMember,[Project].CurrenthierarchyMember))>0
        ),
      --set of issues in filtered projects
       DescendantsSet(
        [Issue].CurrentHierarchyMember,
        [Issue].[Issue])
       ),
      Not IsEmpty([Measures].[Issue Custom date])
    ),
    CASE 
      WHEN [Measures].[Issues resolved]>0
    THEN
      DateDiffdays(
        [Measures].[Issue Custom date],
        [Measures].[Issue resolution date]
      )
    END
  )
  ELSE
  --general iteration from issues
  Avg(
    Filter(
      DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
      Not IsEmpty([Measures].[Issue Custom date])
    ),
    CASE 
      WHEN [Measures].[Issues resolved]>0
      THEN
      DateDiffdays(
        [Measures].[Issue Custom date],
        [Measures].[Issue resolution date]
      )
    END
  )
END

N.B., you should replace the Issue Custom date with the name of the measure you imported. Usually, it starts with the prefix “Issue…”

With the function DateDiffDays and Avg, it will calculate the average difference between issue date picker field and resolution date at the period when issues were resolved.

Martins / eazyBI team

3

Hi, @martins.vanags! Thanks for you answer. I have exactly the same issue, and the formula you provided works perfectly when I use it with Issue dimension, but it doesn’t work when I try to use Time dimension to calculate average resolution time per month, for instance. Could you please give a clue what should I do? Should I create a custom calculated member? Or somehow improve this formula?

4

Hi @Anastasiia_Vasiura

The same formula should have also worked without the “Issue” dimension in the report.
If it doesn’t happen for some reason, you could contact support@eazybi.com and provide your report definition exported to a text file.

Martins / eazyBI