Exclude period from days in status

Questions & Answers
,
1

Hi!
i have a problem:
i have issue status (name HOLD)
and i want to see days in this status between two specified dates in formula
or
i have formula that shows days between two statuses (status1, status2) and i need to exclude days in HOLD status from this formula
(HOLD can be transitioned from any status, many times, so i need to count hold time only in time between status 1 and status 2)


Cache(
  NonZero(
      Avg(
          Filter(
              Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
              (
                  [Measures].[Transitions to status],
                  [Transition Status].[status2]
              ) > 0
              AND
              DateInPeriod(
                  (
                      [Measures].[Transition to status first date],
                      [Transition Status].[status2],
                      [Time].CurrentHierarchy.DefaultMember
                  ),
                  [Time].CurrentHierarchyMember
              )
          ),
          DateDiffWorkdays(
              (
                  [Measures].[Transition to status first date],
                  [Transition Status].[status1],
                  [Time].CurrentHierarchy.DefaultMember
              ),
              (
                  [Measures].[Transition to status last date],
                  [Transition Status].[status2],
                  [Time].CurrentHierarchy.DefaultMember
              )
          )
      )
  )
)

thx for helping.

2

Hi @Tony
Welcome to eazyBI community!

I recommend doing the following steps

  1. By using issue cycles functionality, determine your cycle which excludes status “hold” and includes those statuses you want to consider.
    For example purposes, I have created Issue Cycle with statuses “Reopened”, “In Progress” and “To do”
    4ac07456ce58078dd13c05daed443fbf.png

  2. In your Status dimension, create a new calculated member . Here you should list the statuses that are to be taken into consideration that Status 2 has ended.
    For example purposes, I have created the following member.

Aggregate(
  {[Status].[Done],
  [Status].[Resolved]}
)
  1. Then proceed with defining a new calculated measure replacing “Average “cycle” days”, where “cycle” is the name you have given to your cycle in step 1. and the calculated member “Status to be considered issue has left the cycle” to be replaced by the calculated member name you created in step 2.
(
  --cycle between status 1 and status 2 excluding HOLD
  [Measures].[Average <cycle> days],
  --in Status dimension aggregate all statuses that are considered after status 2
  [Status].[Status to be considered issue has left the cycle]
)

Here is an example of how the report might look. The formula calculates the average days issues spent in the defined statuses (in step1) and calculates the average days only for those issues with current status defined in step 2.

9768bf1f5d26cd9cf93fd139beda9fa0.png
9768bf1f5d26cd9cf93fd139beda9fa0.png600×541 78 KB

​
​Best wishes,
​Elita from support@eazybi.com