Create a report that shows the amount of delayed stories in an Epic

Questions & Answers
,
1

Hi eazybi support and community,

I am trying to create a report in eazybi that counts the delayed stories within an epic. The report should consist of 4 columns:

  • Column 1 = epic
  • Column 2 = epic due date
  • Column 3 = amount of overdue stories within the epic
  • Column 4 = due date of the story that has the “latest” due date within the epic

Overdue means simply that the due date of the story is later than the due date of the epic.

Does someone know how the information for column 3 and 4 can be included in the report?
I highly appreciate your support! :slight_smile:

Best regards
Sylwia

2

Hi @Sylwie

Welcome to the Community! :100:

Please try defining new calculated measures in the Measures dimensions with the following formulas.

Column 3:

CASE WHEN
[Issue].CurrentHierarchyMember.Level.Name = "Epic"
THEN
  Sum(
    Filter(
      ChildrenSet([Issue].CurrentHierarchyMember),
      [Measures].[Issue type] = "Story"
      AND
      DateCompare(
        [Issue].CurrentHierarchyMember.Get('Due date'),
        [Issue].CurrentHierarchyMember.Parent.Get('Due date')
      ) > 0
    ),
    (
      [Measures].[Issues with due date],
      [Issue Type].[Story],
      [Time].CurrentHierarchy.DefaultMember
    )
  )
END

Column 4:

CASE WHEN
  [Issue].CurrentHierarchyMember.Level.Name = "Epic"
THEN
  Order(
    Filter(
      ChildrenSet([Issue].CurrentHierarchyMember),
      [Measures].[Issue type] = "Story"
      AND
      DateCompare(
        [Issue].CurrentHierarchyMember.Get('Due date'),
        [Issue].CurrentHierarchyMember.Parent.Get('Due date')
      ) > 0
    ),
    [Measures].[Issue due date],
    DESC
  ).Item(0).Get("Due date")
END

For these formulas to work, please use the Issue dimension in Rows with the Epic level selected from the Epic hierarchy.
You can read more about the functions used in the formulas here: MDX function reference
​
Best regards,
​Nauris / eazyBI support