Time spent in done issues in Epic

Questions & Answers
1

Hello,
I’m trying to get the total time spent in the done issues in an epic (subtasks included), I set Issues as rows filtered by type (=Epic) and wrote down a measure but getting nowhere.

case
WHEN [issue].CurrentHierarchyMember.get(‘Issue Type’) = ‘Epic’
THEN
sum(ChildrenSet([Issue].CurrentHierarchyMember), ([Measures].[Hours spent with sub-tasks],[Status.Category].[Done]) )
else
[Measures].[Issues created]
END

help please?
I guess it’s something related to epics and issues not being in true parent-child relationship…

[Edit] I’m trying an alternative…

I used the [issue] Epic hierarchy on rows, but this way I can’t manage to filter on a property of a linked issue of the Epic.
I created an issue link dimension called “commissioned by” and “commissions”, but it seems that [issue].currenthierarchymember.getlinkedmember(‘property’) doesn’t get me anywhere.

2

Usually we would recommend using “Issue.Epic” hierarchy from “Issue” dimension and then a simple calculation that returns “Hours spent” from all epic children (incl subtasks) having the particular status category

(
  [Status.Category].[Done],
  [Measures].[Hours spent]
)

Then, you could use it with the “epic” level from the Issue.epic hierarchy and it would return all hours from epic + hours from epic’s children and children’s subtasks

But the second part of the requirement probably is not clear to me yet. And probably this where the complexity starts in your requirement.

You mentioned that you want to filter on property. Could you explain that part in more detail? What is that property (which type of field and on which level you enter values in Jira for that field - epic or children)?
Also, how did you define link both dimensions exactly? Are these dimensions imported from epic links or epic’s children links.

Martins / eazyBI

3

Hello @martins.vanags and thanks again for the suggestions.
Before your answer I tried exactly what you wrote, using the issue.epic hierarchy and using hours spent as you mentioned it.
I happened to be using the resolution instead of the status category, and not all my project are so truthful when it comes to setting the resolution so I was having issues with that.

I also solved the issue links thing, the property is on an issue linked to the Epic via a “commissioned by” link that I imported.
I had to - I read some more documentation about it - specify the [issue].[issue] hierarchy level on the getlinkedmember function for it to work on the issue.epic dimension.

So my measures now look like this:

Epic commissioned track type: 
[Issue].CurrentHierarchyMember.GetLinkedMember('commissioned by',[issue].[issue]).get('Track Type')

Epic level hours spent in done issues:
([Status.Category].[Done],[Measures].[Hours spent])

Total closing workdays:
Sum(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    [Measures].[Issues closed]>0
  ),
  DateDiffWorkdays(
    [Issue].CurrentMember.Getdate("Created at"),
    [Issue].CurrentMember.GetDate("Closed at")
  )    
)

Average Effort / Elapsed Ratio:
([Measures].[Epic level hours spent in done issues]/8)/[Measures].[Total closing workdays]

aaand… it seems to work.
Thanks again for the insight.

1 Like