Count stories with 'open' status in epics

Hi all,
I am very new to eazyBI and to MDX so i hope you can help. I am trying work out if an epic has any open stories in them, if so set epic end date to null, if all stories are closed then get that date of the transition to closed of the last story and set that as the end date.

This is what I currently have.

IIf(Count(
  Descendants([Issue.Epic].CurrentHierarchyMember, [Issue.Epic].[Story]),
  [Status.Category].CurrentMember.get('Status'), "Open") >= 0,"",
Order(
  --all sprints related to epic or epic issues
  Filter(
    [Sprint].[Sprint].Members,
    [Sprint].CurrentHierarchyMember.Name <> "(no sprint)" AND
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember) > 0
  ),
  --order sprints descending by end date
  [Measures].[Sprint end date], 
  BDESC
--get the latest end date
).item(0).get('End date'))

Hi @JohnB,

Welcome to the eazyBI community :tada: !

I recommend using the Epic Link dimension in the report rows. That way, any measure used in the report won’t account for the Epic itself. After that, define a new calculated measure that checks whether there are any “Open issues” in the Epic. If there is, return the last transition to status “Closed” date. The formula could look similar to the one below:

IIf(
  [Measures].[Open issues] > 0,
  ([Measures].[Transition to status last date],
  [Transition Status].[Done]),
  NULL
)

Use the “date” formatting for the calculated measure. The result could look similar to the one below:

Please look at our documentation page for more information on defining calculated measures - ​Calculated measures and members.

Best,
Roberts // support@eazybi.com

Thank you for replying, that has helped me achieve my goal

1 Like