Cumulative number of working days issues had been in a status in given weeks

Questions & Answers
,
1

Hi. I’m struggling to figure out if the following report is achievable using easyBI.

I would like to show the cumulative number of working days issues had been in a status in the given weeks, for all issues that were not resolved in those weeks. So, if in at the end of week 1 an issue had been In Progress for a week, it would add 5 working days to the In Progress status. In week two, the same issue is still In Progress for the whole week and so another 5 days are added to the In Progress bar. The issue is closed in week 3 and so its In Progress days no longer add anything to the In Progress column.

Has someone built a similar report that I could learn from?

2

You can create this calculated measure over set of issues. The formula is quite complicated. Please take into account, the report could be very slow and will work in accounts with a small issue set only.

The formula calculates cumulative time in all previous transitions to this status for each period. If an issue is in this status in a period the formula checks for the last transition date to this status (it could be in this period or any previous period) and will count days from this last transition time in the period till the period end.

NonZero(AVG
(Filter(
 Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
     IIF([Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember,
     1,
-- filters only open issues in period by properties
 DateBeforePeriodEnd(
   [Issue].CurrentHierarchyMember.Get('Created at'),
   [Time].CurrentHierarchyMember)
 AND
 ( isEmpty( [Issue].CurrentHierarchyMember.get('Resolved at'))
 OR
   DateAfterPeriodEnd(
   [Issue].CurrentHierarchyMember.Get('Resolved at'),
   [Time].CurrentHierarchyMember.PrevMember)))
 ),
CASE WHEN
-- filters issues that is in this status at the end of the period
   [Measures].[Issues history] > 0

THEN
 -- days how long issue has been in this status so far, if issue is in this status at the end of period
 DateDiffWorkDays(
  -- last transition date to this status for this period
   Cache(TimeStampToDate(MAX(
    {PreviousPeriods([Time].CurrentHierarchyMember),
     [Time].CurrentHierarchyMember},
     DateToTimeStamp([Measures].[Transition to status last date])
    ))),
   TimeStampToDate(IIF([Time].CurrentHierarchyMember
     is [Time].CurrentHierarchy.DefaultMember, 
      DateToTimeStamp([Time].CurrentHierarchy.Levels('Day').CurrentDateMember.NextMember.StartDate),
      DateToTimeStamp([Time].CurrentHierarchyMember.NextMember.StartDate))))
 +
     -- cumulative days in transition 
 Cache(SUM(
    {PreviousPeriods([Time].CurrentHierarchyMember),
     [Time].CurrentHierarchyMember},
     [Measures].[Workdays in transition status]
     ))

WHEN
-- filters issues that were in this status during the period but are not longer in this status at the end of period
  [Measures].[Issues history] = 0
THEN
 -- cumulative days in transition 
  Cache(SUM(
    {PreviousPeriods([Time].CurrentHierarchyMember),
     [Time].CurrentHierarchyMember},
     [Measures].[Workdays in transition status]
  ))
END
))

Daina / support@eazybi.com