Customize the Age Interval measure

Questions & Answers
, , , ,
1

Hi Team,

I have created a report where i want to display all open issues by specific date with time dimension. there i want to display the age interval for those open issues till that specific selected date but with age interval measure its calculate the age from issue created date till todays date. But i want the age of an issue should be calculated till the date what i have selected in time dimension instead of today date.

for example. I am displaying all open issue till 30 Sep 2024 then age of an issue should calculate till 30 Sept instead of today date.

2

Hi @Sachin_Dhamale
Welcome to eazyBI Community!

If youd want to display the count of issues that fall in certain ranges, you will need to define multiple measures following the examples I have given below (for range below 10 days and for range between 10 and 20 days), and define more ranges in the formula based on the ranges you want to create.

Measure for range below equal to 10 days

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
      DateBeforePeriodEnd(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember) 
 AND
 IsEmpty([Measures].[Issue resolution date])
  ),
  CASE
  WHEN
  DateDiffDays(
    [Measures].[Issue created date],
       [Time].CurrentHierarchyMember.StartDate
  ) <=10
  THEN 
  1
  END
)

Example with measure for range between 10 and 20 days. Adjust the range (10 and 20 in the CASE statement to amend the range)

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
      DateBeforePeriodEnd(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember) 
 AND
  IsEmpty([Measures].[Issue resolution date])
  ),
  CASE
  WHEN
  DateDiffDays(
    [Measures].[Issue created date],
       [Time].CurrentHierarchyMember.Name
  ) >10 AND
   DateDiffDays(
    [Measures].[Issue created date],
       [Time].CurrentHierarchyMember.StartDate
  ) <= 20
  THEN 
  1
  END
)

See example in the screenshot below - how your report might look.

2198×1292 189 KB

Best wishes,

Elita from support@eazybi.com