# HELP needed with multi-step calculation

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 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

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

Thanks All

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 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

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 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