HELP needed with multi-step calculation

Questions & Answers
1

I am trying to match some calculations from an excel sheet that I am working with. I am close but I need some final help. I’m rolling off my project next week and would love to get this wrapped up today

Here’s my calculated measure…

/* PROGRESS DIVIDED BY EXPECTED PROGRESS */
(
[Measures].[Issues created]
/
IIf(DateCompare([Issue].CurrentMember.get(‘Planned End Date’), “Today”)<0,
[Measures].[Issues created],
[Measures].[Issues created] *
(DateDiffDays(
Now(),
[Measures].[Issue Planned Start]
)
/
DateDiffDays(
[Measures].[Issue Planned End],
[Measures].[Issue Planned Start]
)))
)

/* MINUS .5 TIMES ADD/REMOVED DIVIDED BY TOTAL STORIES /
(
.5 *
(
Abs(
[Measures].[Sprint issues added] -
-[Measures].[Sprint issues removed]
) /
[Measures].[Issues created]
)
)
/ SUBTRACT C15 */

(
.05 *
[Measures].[Issue created],
[Issue Type].[Risk],
[Status].[In Progress]

.01 *

[Measures].[Issue created],
[Issue Type].[Risk],
[Status].[In Progress]

)

This is in my excel

image
image1135×160 10.9 KB

The ‘risks’ are open risks ( I think I have captured them correctly)

Thanks All

2

Hello Joseph,

You are quite close with this formula. There are some points I would suggest changing. I assume you would like to use a formula in some overview reports.

The first part uses properties - Planned end date and Planned start date. They work on individual issues only. Therefore this part of the formula at this point works for individual issues, but will not work for overview reports. You would like to use the same formula and apply it to a set of issues and sum it up from there.

The next update is for counting risks. You are addressing created issues with issue type risk, in status In progress. However, you would like to wrap this in brackets. Then it would be a tuple and will pull in this particular count of issues.

Here I updated the formula with those two changes:

(
[Measures].[Issues created]
/
-- progress / expected summed up from all issues
Sum(
  -- access all issues
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  -- add the formula within a list of issues
 IIf(DateCompare([Issue].CurrentMember.get('Planned End Date'), "Today")<0,
[Measures].[Issues created],
[Measures].[Issues created] *
(DateDiffDays(
Now(),
[Measures].[Issue Planned Start]
)
/
DateDiffDays(
[Measures].[Issue Planned End],
[Measures].[Issue Planned Start]
)))
)
)
-(
.5 *
(
Abs(
[Measures].[Sprint issues added] -
-[Measures].[Sprint issues removed]
) /
[Measures].[Issues created]
)
)
-
(
.05 *
-- wrap this in brackets to get it working as a tuple
([Measures].[Issue created],
[Issue Type].[Risk],
[Status].[In Progress])
+
.01 *
-- wrap this in brackets to get it working as a tuple
([Measures].[Issue created],
[Issue Type].[Risk],
[Status].[In Progress])
)

The formula above should give you some results for overview reports. I did not revise any logic there.

Here are some additional points, you can consider. I noticed you are using three basic measures in this formula - Issues created, Sprint issues removed, Sprint issues added. While it might work, you would like to pay attention to a difference in behavior in those measures when used for sprint reports, that might lead to unexpected results.

Measures Sprint issues added and Sprint issues removed represent scope changes in the sprint. It sums how many times issues were added/removed from the active sprint.

Measure Issues created is the most general measure you might want to use for many reports to count a total count of issues. However, if you are using this with a sprint eazyBI will show only issues in the currently active sprint or issues resovled in the sprint. You can check if the measure Sprint issues at closing or maybe even some custom measure counting any issues that were in active sprint might work better to get total issues in the sprint:
Issues in active sprints:

([Measures].[Transitions to issues count],
 [Transition Field].[Sprint status],
 [Sprint Status].[Active])

Daina / support@eazybi.com

3

Thanks for the reply. When I try your code I am getting Infinity% as the value each row. That doesn’t seem correct?

These are values that I have as an example.

Planned Start 5/5/2021
Planned End 1/1/2022
# of stories 75
# of stories completed 43
# of stories removed/added 48
# of Open Critical Risks 0
# of open High Risks 0
Progress 43.00
Expected Progress 75.00
Churn 0.32
Risks 0.00
Churn Factor 0.50
Critical Risk Factor 0.05
High Risk Factor 0.01
Initiative Score 0.25
![image 625x500](upload://5quRQ3rdfcU5X4pDoq7spPHmvvJ.png)
4

Infinity% comes from a division by zero. There could be a case, the formula results as 0 or empty is used as a division. There could be either some problem in the formula leading to empty results or this is a valid case when the formula might give 0. In the latter, you would like to add additional validation to check if the value is not 0 or empty before using it in the division.

You can try to debug what part of the formula gives 0 and check if this is expected or there are some problems in the formula.

Once you detected this, you can consider adding some validations in needed/ Here is one report example with measure Issues resolved % using a simple formula with validation to avoid division by zero.

If you would like more assistance on this, consider reaching out support@eazybi.com. Please send more details about the report and some details about the Jira setup related to the data used in the report. Please send the screenshot of the report and the report definition. We will lead you from there.

Daina / support@eazybi.com