Custom Sprint metrics

Questions & Answers
1

Hello,

I’m trying to build custom report which would should following metrics for each sprint:

  1. Committed Story Points
  2. Added Story Points
  3. Resolved Story Points from Original Commitment
  4. Resolved Story Points from Added Commitment

Sample chart that I want to get out of EazyBI:
image

I’m struggling with calculated measures as EazyBI doesn’t offer those out-of-the-box:

  1. Committed Story Points - I’m using [Measures].[Sprint Story Points committed] which suits my needs.

  2. Added Story Points - [Measure].[Sprint Story Points added] doesn’t seem to return all actual Stories that are added to a specific sprint. Some stories fall under other measures like [Sprint Story Points change] or [Sprint commitment changes] and I can’t get a full list of all Stories that were added to a sprint outside original commitment. I have defined a new calculated measure which iterates through all issues and sums Story points in Sprint that are not from Original commitment:

    NonZero(Sum(
    – set of issues added in a sprint after it was started
    Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    – filter conditions for issues
    IsEmpty([Measures].[Sprint issues committed])
    AND [Measures].[Sprint Story Points at closing] > 0
    ),
    – sum up story points
    ([Measures].[Story Points change],
    [Sprint].DefaultMember)
    ))

  3. Resolved Story Points from Original Commitment - there’s no such measure in EazyBI to filter resolved Stories from original commitment. I’ve created a calculated measure for that:

    NonZero(Sum(
    – set of issues committed AND resolved in a sprint
    Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    – filter conditions for issues
    [Measures].[Sprint issues committed] > 0
    AND [Measures].[Story Points resolved] > 0
    ),
    – sum up story points
    ([Measures].[Story Points resolved],
    [Sprint].DefaultMember)
    ))

  4. Resolved Story Points from Added Commitment - again, no such measure to filter resolved Stories from added commitment. I’ve created a calculated measure for that:

    NonZero(Sum(
    – set of issues added AND resolved in a sprint
    Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    – filter conditions for issues
    IsEmpty([Measures].[Sprint issues committed])
    AND [Measures].[Story Points resolved] > 0
    ),
    – sum up story points
    ([Measures].[Story Points resolved],
    [Sprint].DefaultMember)
    ))

These newly created calculated measures seem to do what I need in one sprint context when I have Issue dimension in Rows and Sprint dimension in Pages. But once I start using Sprints as Rows it fails to return results for each Sprint. Usually queries take too long to process.

Can someone help me figure out what I am doing wrong and how to optimize these measures, so that they perform better.

Thank you in advance.

2

Hi @rolandas.kicas,

Welcome to the eazyBI community!

I am happy you tried to create the calculated measures for the missing metrics on your own. Please have a look at our Demo account. There is one particular example that could fit your use case - https://eazybi.com/accounts/1000/cubes/Issues/reports/243468-sprint-issue-balance. It has the measure “Sprint issues completed of committed”, which displays the number of resolved issues from the original commitment. Please try to alter the formula to use the number of Story points instead:

-- this measure will calculate the resolved issues in the last Sprint where an issue was committed and finally resolved
-- this measure excludes committed and completed issues if they were reopened after sprint completion and were resolved in later Sprint or outside of Sprint
CASE WHEN
    [Sprint].Currentmember.Level.name = "Sprint"
THEN
  NonZero(SUM(
    Filter(
      Descendants([Issue].Currentmember, [Issue].[Issue]),
       [Sprint].CurrentMember.Name = [Measures].[Issue Sprint]      
      ),
      CASE WHEN
      [Measures].[Sprint Story Points committed] > 0
      THEN
      [Measures].[Story Points resolved]
      END
  ))
END

Please try to create the calculated measure, to calculate the added resolved Story points.

The Sprint dimension DefaultMember in your calculated members likely caused the measure malfunction with Sprint dimension on rows.

The measure “Sprint Story Points added” calculates the number of Story Points that were in issues that were added to the Sprint while it was “Active”.
The “Sprint Story Points commitment change” displays the difference in Story Points at the end and the beginning of the Sprint.

If a Story was in the Sprint before its beginning (Sprint Story Points committed), but the value of Story Points was changed, those will fall into “Sprint Story Points commitment change” not in “added” or “removed”.

Please have a look at our documentation page for more information on all the Sprint scope measures - https://docs.eazybi.com/eazybijira/data-import/data-from-jira-and-apps/jira-software-custom-fields#JiraSoftwarecustomfields-Sprintscopemeasures.

Best,
Roberts // support@eazybi.com

3

@roberts.cacus, thanks so much for the guidance. I managed to create the report I need. Thanks again.

1 Like