Sum a custom measure for closed issue

Please don’t lecture on Agile process, this works for us.

Background:
We have a simplified Story point system where a point = 1 Jira day (6hrs). We are a bug fix shop, so a chunk of our issues are closed in hours. We were using reports, but our story point variance is off as the sum of hours under reports points as we are using the [Measures].[Hours spent closed].

So, we created a custom field that takes issue.fields.timespent does some math to convert it to days and then timesp=Math.ceil(timesp). This works perfectly for our use.

Problem:
I know I am missing something basic with my attempt to create a report that sums this for the closed issue in the sprint. The measure I am creating works fine at the issue level (Drill Across → Issue) but it seems to be summing way too many hours:

Sum(
Filter(–Itterate through all closed items in selected period
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Issue status] = “Closed”),
[Issue].CurrentHierarchyMember.Get(‘Time in Days’))

Time should be close to Out of box Story Points closed.

Hi, @EdP

Welcome to the eazyBI community.

Well done with the MDX formula. In this case, you need a context measure, “Issues closed,” to filter the context of the hours spent.

The part with the Descendants is correct, but we have a faster solution where we go through the projects and then issues. So I’m including that in the formula.

The formula should look something like this:

``````Sum(
Filter(–Itterate through all closed items in selected period

Generate(
--set of relevant projects
Filter(
[Issue].[Project].Members,
[Measures].[Issues created]>0
),
--set of issues in filtered projects
DescendantsSet(
[Issue].CurrentHierarchyMember,
[Issue].[Issue])
),

[Measures].[Issue status] = "Closed"
),
CASE WHEN
[Measures].[Issues closed]>0
THEN
[Issue].CurrentHierarchyMember.Get('Time in Days')
END
)
``````

Kindly,
Ilze support@eazybi.com