Use Epic Target End When Story Target End is Empty

[MichaelW]

Good morning!
Background:
I’m creating a version burnup and adding a custom “planned story points” line. The way it works in my head is I plot story points along a timeline based on the issue’s target end. It works great if every single story has a target end associated with it. However, deeper in the backlog there are a number of stories with no target end. To solve this, I’d like an MDX measure that would identify if the story has a target end. If target end is not populated, it would pull the target end from the Epic.
What I’ve tried:

1. Show epic children based on a custom field that only exists in the Epic

I tried Zane’s suggestion (#2) and added this to my advanced settings:

[jira.customfield_10304]
update_from_issue_key = "epic_key"

It looked like it worked because I can see “Target End” on stories that didn’t have it previously. But, when I plotted it on the timeline with the “Story Points with Target End” measure, it didn’t recognize any of the inherited dates and I was back at square one.
2. Creating my own custom measure
Not quite. Here’s the MDX for the measure:

CASE WHEN [Issue.Advanced Roadmaps].CurrentMember.Level.Name = 'Story'
AND
IsEmpty([Measures].[Issue Target end])
THEN
Sum(
  Filter(
    Descendants([Issue.Advanced Roadmaps].CurrentMember,[Issue.Advanced Roadmaps].[Story]),
    DateInPeriod(
      [Issue].CurrentHierarchyMember.Get('Target end'),
      [Time].CurrentHierarchyMember
    )
  ),
  ([Measures].[Story Points with target end],
  [Time].CurrentHierarchy.DefaultMember)
)
ELSE
Cache(
  NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
    [Measures].[Story Points with target end]
  ))
  + [Measures].[Story Points with target end]
)
END

What I was attempting is to create a measure that evaluates if it is a story AND it has a target end. If it did not, then I would like the measure to pull the Target End from the Epic and use it to plot story points on the timeline. Any thoughts? Thank you!!

Note: I’m expecting about 600 more story points than what is mapped here.

[roberts.cacus]

Hi @MichaelW,

The issues inherit the date property value, but the date-based measures, like “Issues with target end” and “Story Points with target end”, don’t. To retrieve the date from the parent of the “Story” level, I recommend the following calculated measure formula:

Sum(
  Filter(
    Descendants([Issue.Advanced Roadmaps].CurrentMember,[Issue.Advanced Roadmaps].[Story]),
    DateInPeriod(
      CoalesceEmpty(
        [Issue].CurrentHierarchyMember.Get('Target end'),
        [Issue].CurrentHierarchyMember.Parent.Get('Target end')
      ),
      [Time].CurrentHierarchyMember
    )
  ),
  ([Measures].[Story Points created],
  [Time].CurrentHierarchy.DefaultMember)
)

This formula will look through “Story” level issues of the “Advanced Roadmaps” hierarchy and replace the empty Story “Target end” date with its parent “Target end”. Otherwise, it will use the Stories date. I recommend summing the “Story Points created” instead of “Story Points with target end” as that will include Stories and their point without the “Target end” date.

To accumulate the values, you would need to use the calculated measure above in a new calculation with PreviousPeriods().

Best,
Roberts // support@eazybi.com

[MichaelW]

Worked like a charm! Thank you so very much!!