Epic target start/end dates based on stories assigned to sprints

Hello,

I’m trying to create a Gantt chart for tracking epic completion during five upcoming sprints. Each epic has stories that are pulled to complete in sprints, but an epic itself is NOT assigned to any sprint. One epic may take multiple sprints to complete, based on how stories are spread across sprints.

I want to create two calculated measures:

  • Epic target start date - earliest sprint start date based on epic stories assigned to upcoming sprints
  • Epic target end date - latest sprint end date based on epic stories assigned to upcoming sprints

Can someone help with a definition of the above?

Thank you in advance.

1 Like

Hi @rolandas.kicas,

Even though epic is not assigned to any sprint, you can see to which sprints it is associated through its stories. Using Epic Link dimension (or Issue dimension with Epic hierarchy), then standard measures like Issues created, Story Points created represent aggregated values for the epic children. And those measure values could be split across the Sprints related to epic stories (see picture below)

You may use this relation to find the start date of the earliest sprint and end date of the latest sprint.
The formula for calculated measure Epic target start date might look like this:

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 ascending by start date
  [Measures].[Sprint start date], 
  BASC
--get the earliest start date
).item(0).get('Start date')

Set measure formatting to date mmm dd yyyy Month Day Year.

If you would like to look only on the future sprints, then you may add one more filtering criteria for the Sprint members that sprint is not closed: NOT [Sprint].CurrentMember.GetBoolean("Closed")
But be cautious with this approach as future sprints might not have start and end dates.

The formula for calculated measure Epic target end date might look like this:

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')

For the report, use dimension Epic Link (or Issue dimension with Epic hierarchy) on rows and new calculated measures for dates on columns. The report might look like in the picture below:

Best,
Zane / support@eazyBI.com

Zane, thanks a lot for your help. This is exactly what we needed.

Hello All, thank you for this helpful piece of code. I tried to modify it so that it would sort the sub-tasks (stories or defects, in my case) and order by Sprint Name. Unfortunately I am not sure if my understanding of the hierarchies and/or members are correct, because I am not getting what I want - the code seems to just return a randomly sorted sprint name and attach to the epic.

Here is a screenshot of the table I setup:

Here is the MDX definition for the Calculated Measure “Epic target end Sprint”:

Blockquote
Order(
–all sprints related to epic or epic issues
Filter(
[Sprint].[Sprint].Members,
[Sprint].CurrentHierarchyMember.Name <> “(no sprint)” AND
–[Sprint].Name MATCHES ‘Wannabe_’ AND
[Measures].[Issues created] > 0
),
–order sprints descending by end date
[Measures].[Issue Sprint],
DESC
–get the latest end date
).item(0).Name

Blockquote

Could anyone please let me know what I am doing wrong?

Thanks!
carlos.