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

rolandas.kicas · February 28, 2020, 9:35am

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.

zane.baranovska · March 11, 2020, 4:55pm

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

rolandas.kicas · March 12, 2020, 11:53am

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

carlos · April 28, 2020, 10:36am

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.

acreech · August 12, 2022, 7:38pm

@zane.baranovska,

thank you for providing these calculated members. They are almost perfect, but I think there is an issue with Closed/Completed Sprints.

In the below screenshot you will see that the Epic target start date from YAM-155 and YAM-156 are blank. However in the properties of those issues, they have completed sprints with past dates.

The start date for Epic YAM-154 should have been 7/13/22 and the end date should have been 8/9/22.

I have included the formula I implemented from your previous comment below. Can help me understand what I may have done wrong?

Missing sprint dates

YAM-155

YAM-156

Epic target start date

Epic target end date

acreech · August 16, 2022, 12:32pm

@zane.baranovska ,
I watched your video on troubleshooting the MDX and worked through breaking down the above MDX.
I have now figured out that counting by Sprint hierarchy won’t work, because that only takes into account an Active sprint and not completed sprints. To get at the target start date based on ALL sprints, I will need to look at modifying the formula to use the Issue Hierarchy with the property Issue Sprints. I am not sure how I will get from their to the Sprint dates of those closed sprints, but that is the next task. I realized this by using your method below and saw the break when examining the below section of the Filter.

Filter(
    [Sprint].[Sprint].Members,
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember) > 0

zane.baranovska · August 18, 2022, 12:12pm

Hi @acreech,
Measure “Issues created” represent issue relation with their current sprint or the last sprint in which issue was completed. If an issue was not completed during the assigned sprint, then the calculation does not see the relation between the issue and the sprint.

If you would like to use the expression to show also historical sprints, you might want to use Sprint scope measures “Sprint Issues at closing” .

Order(
  --all sprints related to epic or epic issues
  Filter(
    [Sprint].[Sprint].Members,
    [Sprint].CurrentHierarchyMember.Name <> "(no sprint)" AND
    --check if current sprint or past sprint
    (
      ([Measures].[Issues created],
      [Time].CurrentHierarchy.DefaultMember)
      +
      ([Measures].[Sprint issues at closing],
      [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')

Best,
Zane / support@eazyBI.com

acreech · August 23, 2022, 12:39pm

this was perfect, thank you!

Also for anyone returning to this thread later, here is the Epic target Start date

Order(
–all sprints related to epic or epic issues
Filter(
[Sprint].[Sprint].Members,
[Sprint].CurrentHierarchyMember.Name <> “(no sprint)” AND
–check if current sprint or past sprint
(
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember)
+
([Measures].[Sprint issues at closing],
[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’)