Masures for open issue for more then 14 days and one for issues open less then 14 days

would like to create two masures for open issue
one for more then 14 days and one for issues open less then 14 days

divided per week
can you please assist?

Hi @Hadar_Pinko,

You might use the interval dimension “Age interval” to split issues by their current age intervals.
Please read more about interval dimensions here - Interval dimensions.

However, suppose you want to split the issues by their age at a specific historical time period. In that case, you might need to create calculated measures that check for the issue’s creation date and resolution date related to a current member in the Time dimension.

Please see below the MDX expression for a measure for Open issues not older than 14 days at the start of the reported time period.

Sum(
  Filter(
--iteration through the issues
   DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
--condition 1 - not resolved before period start
  IIF(IsEmpty([Issue].CurrentMember.Get('Resolved at')),
      1,
      DateDiffDays([Issue].CurrentMember.Get('Resolved at'),
                   [Time].CurrentHierarchyMember.StartDate)<0)
 AND
--condition 2 - not older than 14 days at start of time period
    DateDiffDays([Issue].CurrentMember.Get('Created at'),
                 [Time].CurrentHierarchyMember.StartDate)
     <=14),   
 [Measures].[Open issues])

And here is the expression for open issues older than 14 days.

Sum(
  Filter(
--iteration through the issues
   DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
--condition 1 - not resolved before period start
  IIF(IsEmpty([Issue].CurrentMember.Get('Resolved at')),
      1,
      DateDiffDays([Issue].CurrentMember.Get('Resolved at'),
                   [Time].CurrentHierarchyMember.StartDate)<0)
 AND
--condition 2 - older than 14 days at start of time period
    DateDiffDays([Issue].CurrentMember.Get('Created at'),
                 [Time].CurrentHierarchyMember.StartDate)
     >14),   
 [Measures].[Open issues])

Kind regards,
Oskars / support@eazyBI.com

1 Like