Dead Time Average

Hello together,

I am new to Easybi. So excuse my ignorance :slight_smile:

I want to create a measure that will calculate the Dead Time Average for each month.
Currently, I always get the same number for each month:

I have created the following:

My created Measures:
[Measures].dead time average
Avg(
Filter(
[Issue].[Issue].Members,
[Measures].[dead time] > 0
),

[Measures].[dead time]
)

[Measures].[dead time]
DateDiffHours(
[Measures].[created at with time], [Measures].[first assignee date]
)

[Measures].[created at with time]
[Issue].CurrentHierarchyMember.get(ā€˜Created at’)

[Measures].[first assignee date]
TimestampToDate((
[Measures].[Transition from first timestamp],
[Transition Field].[Assignee],
[Time].CurrentHierarchy.DefaultMember
))

Hi @Luke,

The calculated measure returns the same result because all calculation elements ignore the current Time dimension member. The issue property ā€œcreated at with timeā€ is not tied to the Time dimension, and the ā€œfirst assignee dateā€ references the Time dimension default member.

Also, I don’t recommend iterating through the Issue dimension ā€œIssueā€ level members with the particular expression. Instead, use the Descendants() function and use an issue property as the first filter condition, a numeric measure to tie the calculation to the report context.

Suppose you want to tie the result to the issue creation date on the Time dimension. In that case, the calculated measure formula would look similar to the one below:

Avg(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    -- issue created in Time dimension period
    DateInPeriod(
      [Issue].CurrentMember.Get('Created at'),
      [Time].CurrentHierarchyMember
    )
  ),
  CASE WHEN
    -- issue assignee has changed
    ([Measures].[Transitions from assignee],
    [Time].CurrentHierarchy.DefaultMember) > 0
  THEN
    [Measures].[dead time]
  END
)

Please look at our documentation page for more information on defining calculated measures - ​Calculated measures and members.

Best,
Roberts // support@eazybi.com

1 Like

Hi @roberts.cacus,

Thank you very much for your detailed help. :slight_smile: