Issues created before/after version release & Time dimension

[rolandas.kicas]

Hello,

I’m trying to create report which shows Defect Detection rate for each Jira project. The report relies on [Affects Version] dimension and categorizes Defects comparing their creation date vs version release date.

Sample report example below:

Calculated measures:

1. Defects created:
   ( [Measures].[Issues created], [Issue Type].[Defect], [Resolution].[Unresolved or Fixed])

2. Defects created before version release:
   Sum(
   Descendants([Affects Version].CurrentMember, [Affects Version].[Version]),
   Sum( Generate(Ascendants(
   [Time].CurrentHierarchy.Levels(‘Day’).DateMember(
   [Affects Version].CurrentHierarchyMember.get(‘Release date’))
   ),
   IIF([Time].CurrentHierarchyMember IS
   [Time].CurrentHierarchyMember.FirstSibling, {},
   { [Time].CurrentHierarchyMember.FirstSibling:
   [Time].CurrentHierarchyMember.PrevMember })
   ),
   [Measures].[Defects created]
   ))

3. Defects created after version release:
   Sum(
   Descendants([Affects Version].CurrentMember, [Affects Version].[Version]),
   Sum(Generate(Ascendants(
   [Time].CurrentHierarchy.Levels(‘Day’).DateMember(
   [Affects Version].CurrentHierarchyMember.get(‘Release date’))
   ),
   IIF([Time].CurrentHierarchyMember IS
   [Time].CurrentHierarchyMember.LastSibling, {},
   { IIF([Time].CurrentHierarchyMember.Level.Name = ‘Day’,
   [Time].CurrentHierarchyMember,
   [Time].CurrentHierarchyMember.NextMember):
   [Time].CurrentHierarchyMember.LastSibling })
   ),
   [Measures].[Defects created]
   ))

4. Defect detection rate:
   CASE WHEN [Measures].[Defects in version count] > 0
   THEN [Measures].[Defects created before version release]
   / [Measures].[Defects in version count]
   END

5. Defects in version count:
   [Measures].[Defects created before version release] + [Measures].[Defects created after version release]

PROBLEM:
The measures seem to calculate their job correctly until I add [Time] dimension in [Pages]. You can see in the image above, that when [Last 30 days] is selected - measures [Defects created before version release] and [Defects created after version release] do not react to it and still show all Defects that fall under All times category.

Can someone help me adjust these two measures, so that they take [Time] into account?

Thank you in advance.

[rolandas.kicas]

Hello, If anyone knows a better way how to determine whether defect was created prior or post release, please share the formula for calculated measures. It is important, that the measure should take [Time] dimension into account as I want to track defect detection rate trend over time.

The report I am trying to create should track Defect Detection rate for each Project over time. It should analyze Defects created in each Project for a period. If Defect’s creation date is earlier than Affects version’s release date (each issue has such field as mandatory), than it is a prior-release defect. Otherwise, if Defect’s creation date is equal or later than Affects version’s release date, then it is post-release defect.

Thank you in advance.

[daina.tupule]

You are using a formula that overrides (ignores) Time selection in the report.

The first formula addresses any time before a version release date and the second formula address any time for any version after version release date.

Both formulas work on time hierarchy default structures. The first formula is the older version (exact formula) of eazyBI function PreviousPeriods. You used the same approach to create a formula to retrieve next periods (we do not have the function for this).

However, in your case, you would like to pull in the selected period and split the period in days till version release and after version release.
I added an additional case to the formula to add all created issues to a period if the version was released after the selected period. This is a performance improvement in the formula, to avoid cases when we unnecessary validate any period with any version.

Here is a possible formula for Issues created in the period till version release:

Sum(
Descendants([Affects Version].CurrentMember, [Affects Version].[Version]),
  CASE WHEN
  -- for version releaed in period we will split issues created
  DateInPeriod(
    [Measures].[Version release date],
    [Time].CurrentHierarchyMember
  )
  THEN
  SUM(
    Filter(
      -- get all days in selected period
      Generate( 
        { [Time].CurrentHierarchyMember,
        ChildrenSet([Time].CurrentHierarchyMember) },
        Descendants([Time].CurrentHierarchyMember,[Time].CurrentHierarchy.Levels("Day"))),  
     -- filter by days till version release:
     DateCompare(
       [Time].CurrentHierarchyMember.StartDate,
       [Measures].[Version release date]
     ) <=0),
  [Measures].[Defects created])
  WHEN
  -- for a period before version release date we will assign all created issues
  DateCompare(
    Cache(Generate( 
    { [Time].CurrentHierarchyMember,
    ChildrenSet([Time].CurrentHierarchyMember) },
    Descendants([Time].CurrentHierarchyMember,[Time].CurrentHierarchy.Levels("Day"))).Item(0).StartDate),
    [Measures].[Version release date]) <=0
  THEN NonZero([Measures].[Defects created])
  END
)

You can use a similar formula changing the comparison that will count issues created after a version release date.

Daina / support@eazybi.com

[rolandas.kicas]

Hello Daina,

Thank you for the solution which worked perfectly. I adapted the query for Defects created after release. It also performs better than the one I was using for interim period, which uses cross join:

Sum(
Generate(
ChildrenSet([Affects Version].CurrentHierarchyMember),
Descendants([Affects Version].CurrentHierarchyMember, [Affects Version].[Version])),
case when
[Affects Version].CurrentMember.Level.Name = ‘Version’
then
NonZero(count(Filter(Generate(NonEmptyCrossJoin(
[Issue].[Issue].Members,
[Time].CurrentHierarchyMember), [Issue].CurrentMember),
[Measures].[Defects created] > 0 AND
DateCompare(DateParse([Measures].[Issue created date]),
DateParse([Affects Version].CurrentMember.get(‘Release date’))) < 0

)))

end
)

[rolandas.kicas]

Hello Daina, quick question. We are successfully using calculated measure created by you:

Sum(
Descendants([Affects Version].CurrentMember, [Affects Version].[Version]),
  CASE WHEN
  -- for version releaed in period we will split issues created
  DateInPeriod(
    [Measures].[Version release date],
    [Time].CurrentHierarchyMember
  )
  THEN
  SUM(
    Filter(
      -- get all days in selected period
      Generate( 
        { [Time].CurrentHierarchyMember,
        ChildrenSet([Time].CurrentHierarchyMember) },
        Descendants([Time].CurrentHierarchyMember,[Time].CurrentHierarchy.Levels("Day"))),  
     -- filter by days till version release:
     DateCompare(
       [Time].CurrentHierarchyMember.StartDate,
       [Measures].[Version release date]
     ) <=0),
  [Measures].[Defects created])
  WHEN
  -- for a period before version release date we will assign all created issues
  DateCompare(
    Cache(Generate( 
    { [Time].CurrentHierarchyMember,
    ChildrenSet([Time].CurrentHierarchyMember) },
    Descendants([Time].CurrentHierarchyMember,[Time].CurrentHierarchy.Levels("Day"))).Item(0).StartDate),
    [Measures].[Version release date]) <=0
  THEN NonZero([Measures].[Defects created])
  END
)

What it would take to add an additional condition for delayed releases (status is still unreleased, but the original release date already passed) to sum up all issues to ‘created before release’ bucket even those who were created after the original release date?

Thanks in advance.

[daina.tupule]

Here I added an updated formula to check if version in period is released or not. For released version I applied the same split of issues created till version release date. I added a new cycle to pull in unreleased versions with past date or date in period (DateBeforePeriodEnd) and I am pulling all issues created in unresolved versions in those periods.

Sum(
Descendants([Affects Version].CurrentMember, [Affects Version].[Version]),
  CASE WHEN
  -- for version released in period we will split issues created
  DateInPeriod(
    [Measures].[Version release date],
    [Time].CurrentHierarchyMember
  )
  AND
  [Affects Version].CurrentHierarchyMember.Get('Status') <> "Unreleased"
  THEN
  SUM(
    Filter(
      -- get all days in selected period
      Generate( 
        { [Time].CurrentHierarchyMember,
        ChildrenSet([Time].CurrentHierarchyMember) },
        Descendants([Time].CurrentHierarchyMember,[Time].CurrentHierarchy.Levels("Day"))),  
     -- filter by days till version release:
     DateCompare(
       [Time].CurrentHierarchyMember.StartDate,
       [Measures].[Version release date]
     ) <=0),
  [Measures].[Defects created])
  WHEN
  -- for unreleased version with released date in past or in this period pick all created issues
  DateBeforePeriodEnd(
    [Measures].[Version release date],
    [Time].CurrentHierarchyMember
  )
  AND
  [Affects Version].CurrentHierarchyMember.Get('Status') = "Unreleased"
  THEN NonZero([Measures].[Defects created])
  WHEN
  -- for version release date still to come
   DateAfterPeriodEnd(
      [Measures].[Version release date],
      [Time].CurrentHierarchyMember
    )
  THEN NonZero([Measures].[Defects created])
  END
)

I also made a small change and used function DateAfterPeriodEnd instead of DateCompare (used in the formula shared with you in previous answer). This is easier to use and will pick up the same period correctly. We added updates for Time functions in latest versions to work with Time calculated member and multiple time selections.

Daina / support@eazybi.com

[rolandas.kicas]

Thank you Daina,

I was using this self modified formula:

Sum(
  Generate(
    ChildrenSet([Affects Version].CurrentHierarchyMember),
    Descendants([Affects Version].CurrentHierarchyMember, [Affects Version].[Version])),
  case when
    [Affects Version].CurrentMember.Level.Name = 'Version'
  then
    Sum(
    Descendants([Affects Version].CurrentMember, [Affects Version].[Version]),
      CASE 
        WHEN
        -- for unreleased version get all created issues
        [Affects Version].CurrentMember.Get("Status") = 'Unreleased'
        THEN
          NonZero([Measures].[Defects found])  
          
        WHEN
          -- for version releaed in period we will split issues created
          [Affects Version].CurrentMember.Get("Status") = 'Released' AND
          DateInPeriod(
            [Measures].[Version release date],
            [Time].CurrentHierarchyMember
          )
        THEN
          SUM(
            Filter(
              -- get all days in selected period
              Generate( 
                { [Time].CurrentHierarchyMember,
                ChildrenSet([Time].CurrentHierarchyMember) },
                Descendants([Time].CurrentHierarchyMember,[Time].CurrentHierarchy.Levels("Day"))),  
             -- filter by days till version release:
             DateCompare(
               [Time].CurrentHierarchyMember.StartDate,
               [Measures].[Version release date]
             ) <0),
          [Measures].[Defects found])
        WHEN
          -- for a period before version release date we will assign all created issues
          [Affects Version].CurrentMember.Get("Status") = 'Released' AND
          DateCompare(
            Cache(Generate( 
            { [Time].CurrentHierarchyMember,
            ChildrenSet([Time].CurrentHierarchyMember) },
            Descendants([Time].CurrentHierarchyMember,[Time].CurrentHierarchy.Levels("Day"))).Item(0).StartDate),
            [Measures].[Version release date]) < 0
        THEN 
          NonZero([Measures].[Defects found])
        END
     
    )
  end
)

I see the two formulas (mine and yours) return different results, so I will test and respond back. Thanks again for your help with this.

[Kristine]

Is there a sample report of this somewhere that I can export and use as a template?