Average time between creation and Fixed

Questions & Answers
1

Hi everyone,

I`m trying to calculate the average time between issue creation and when the issue is set to Resolution = Fixed

[measure].[time between creation and fixed]
I have a Calculate Measure that get the time between creation and Resolution = Fixed. If the issue is not fixed, it calculates time between issue creation and Now.
I plan on putting resolution in Page to have only issues which are Fixed or Unresolved.

Case When      
  isEmpty(DateDiffdays(
    ([Issue].CurrentHierarchyMember.GetDate('Created at')),
    (CASE WHEN [Resolution].[Resolution].getMemberNameByKey([Issue].CurrentHierarchyMember.get('Resolution ID')) = 'Fixed'
      THEN [Issue].CurrentHierarchyMember.GetDate('Resolved at')
      END)
  )) THEN
  DateDiffdays(
    ([Issue].CurrentHierarchyMember.GetDate('Created at')),
    NOW()
    )
    ELSE
    DateDiffdays(
    ([Issue].CurrentHierarchyMember.GetDate('Created at')),
    (CASE WHEN [Resolution].[Resolution].getMemberNameByKey([Issue].CurrentHierarchyMember.get('Resolution ID')) = 'Fixed'
      THEN [Issue].CurrentHierarchyMember.GetDate('Resolved at')
      END)
  )END

This measure seems to work fine.

[Measure].[Average time between creation and Fixed]
Then I used the previous measure to create my average.

NonZero(
Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[time between creation and Fixed]>0
)
,
[Measures].[time between creation and Fixed]
))

My problem is that no matter what is in Pages or in Row (other than individual issues) I always get the same result for my average

image

image

Can someone help me find the error(s) in my measure or reasoning?
Thanks

2

Thank you @janis.plume for replying to my email!

Posting the answer here so everyone can benefit from it

"
Such behavior is because the “time between creation and fixed” is based on the issue properties, and those cannot adjust to the report page filter. The properties work only for individual issues, and you need to use a measure for the filters to work.

Please, try adjusting the average calculation with an additional condition:

NonZero(

Avg(

Filter(

Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),

[Measures].[time between creation and Fixed]>0

AND

[Measures].[Issues created]>0

)

,

[Measures].[time between creation and Fixed]
"

1 Like