Need Split of Sprint Commitment changes into Added content & Removed Content

apanchal · June 22, 2021, 11:19am

Hello EazyBI Community,

I have created a customized chart for sprint health which takes the commitment numbers (count) after adding 1 day slack. Idea is to give teams 1 entire day to wrap up their planning. So commitment in terms of Issue Count & Story Points is being calculated using a user defined calculated measure which considers Sprint start date + 1 day.

I have following columns in my table: Issues committed, Sprint Issues Completed, Sprint Issues not completed, Sprint Commitment Changes (Count) & % Sprint Health.

I need to split the calculated measure that I created for Sprint commitment changes (count) into 2 columns Issues Added (count) & Issues Removed (count) & the summation of both should be equal to the main column i.e. Sprint commitment changes.

In the above example, when I drill down into story - I see 2 issues were added & then 2 issues were removed & hence the count is 0, which is fine. Now I want to split this into 2 columns Issues Added & Issues Removed which should show the same data i.e. 2 in Issues Added & 2 in Issues Removed.

Issues Committed:
–Count of Issues on sprint start + 1 day slack
(
[Measures].[Issues history],
[Time].CurrentHierarchy.Levels(‘Day’).DateMember(
DateAddDays(
[Sprint].CurrentMember.get(‘Start date’),1
)
)
)

Sprint Commitment changes(count):
[Measures].[Sprint issues at closing] -
[Measures].[Issues Committed]

Requesting help in this case. Thanks in advance.

apanchal · June 25, 2021, 11:06am

Hello EazyBI community, eagerly waiting for any inputs here. Thanks in advance.

daina.tupule · July 16, 2021, 8:36am

I would suggest retrieving all issues added to the sprint and then remove the ones added on the first two days:

( [Measures].[Transitions to issues count],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  -- An issue was added or created in an active sprint
  [Issue Sprint Status Change].[(none) => Active]
)
-
Aggregate(
-- remove first two days:
  {
  [Time].[Day].DateMember([Sprint].CurrentHierarchyMember.Get('Start date')),
  [Time].[Day].DateMember(DateAddDays([Sprint].CurrentHierarchyMember.Get('Start date'), 1))
  },
( [Measures].[Transitions to issues count],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  -- An issue was added or created in an active sprint
  [Issue Sprint Status Change].[(none) => Active]
)  
)

For removed you can use a similar formula with measure Transitions from issues count instead of Transitions to issues count.

Daina / support@eazybi.com

apanchal · October 19, 2021, 11:52am

Thanks a Lot Diana. That really helped.

apanchal · May 20, 2022, 11:39am

Hello @daina.tupule,

How do I achieve the above formula for Story Points?

The above formula is for Issue counts added & then removed after commitment, I would like to have same report where everything is represented in terms of Story Points.

Your help is required urgently, thanks

daina.tupule · June 6, 2022, 2:15pm

The calculations for story points could be similar.

You can switch the measure Transitions to issues count to Story Points added, and measure Transitions from issues count to Story Points removed.

For example, added story points:

( [Measures].[Story Points added],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  -- An issue was added or created in an active sprint
  [Issue Sprint Status Change].[(none) => Active]
)
-
Aggregate(
-- remove the first two days:
  {
  [Time].[Day].DateMember([Sprint].CurrentHierarchyMember.Get('Start date')),
  [Time].[Day].DateMember(DateAddDays([Sprint].CurrentHierarchyMember.Get('Start date'), 1))
  },
( [Measures].[Story Points added],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  -- An issue was added or created in an active sprint
  [Issue Sprint Status Change].[(none) => Active]
)  
)

Daina / support@eazybi.com

apanchal · June 8, 2022, 5:15am

Hello @daina.tupule,

Thanks for the revert. Appreciate the help!

I already have the above implemented but the results of both the formulas are not equal. The one with Count works fine, but the one with story points like you suggested above does not work, it does not match with the above count one.

The formulas for your reference are as below:
Issues Added after commitment:

NonZero(( [Measures].[Transitions to issues count],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  -- An issue was added or created in an active sprint
  [Issue Sprint Status Change].[(none) => Active]
)
-
Aggregate(
-- remove first two days:
  {
  [Time].[Day].DateMember([Sprint].CurrentHierarchyMember.Get('Start date')),
  [Time].[Day].DateMember(DateAddDays([Sprint].CurrentHierarchyMember.Get('Start date'), 1))
  },
( [Measures].[Transitions to issues count],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  -- An issue was added or created in an active sprint
  [Issue Sprint Status Change].[(none) => Active]
)  
))

Story Points Added After Commitment:

NonZero(( [Measures].[Story Points added],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  -- An issue was added or created in an active sprint
  [Issue Sprint Status Change].[(none) => Active]
)
-
Aggregate(
-- remove first two days:
  {
  [Time].[Day].DateMember([Sprint].CurrentHierarchyMember.Get('Start date')),
  [Time].[Day].DateMember(DateAddDays([Sprint].CurrentHierarchyMember.Get('Start date'), 1))
  },
( [Measures].[Story Points added],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  -- An issue was added or created in an active sprint
  [Issue Sprint Status Change].[(none) => Active]
)  
))

From my analysis, I found that:

19391 Story - Was moved to future sprint before the sprint started & 2 days after the sprint started, the story was moved back into sprint 121. Also, the story points were added only on 8th April i.e. 2 days after being added to the sprint.

19394, 19395 stories - 2 days after the sprint started, the story was moved from the future sprint back into sprint 121. Also, the story points were added only on 8th April i.e. 2 days after being added to the sprint.

So ideally as it is captured in the Issues Added after commitment formula, it should also be shown in Story points added after commitment. Is it because the issues are added from future sprint or is it due to story points added later on?

Awaiting urgently for your revert.

apanchal · June 30, 2022, 4:46am

Hi @daina.tupule awaiting your revert on this one. Thanks.

Also, I wanted to make sure that all kinds of issue movements are captured in both formulas i.e Issue added after commitment & story points added after commitment. i.e. if the issues is moved from None to Active, from Future to Active.

daina.tupule · July 8, 2022, 9:28am

The shared formulas work for simpler cases when we need to split when issues with story points were added to the sprint.

However, your case seems more complex. You would like to count not only the case when issues were added, but also added points or removed points to already added issues.

Therefore, I would suggest considering more complex formulas counting the difference of story points at closing with the status at the end of the second day. If points on the second day are more than on closing - they should be treated as removed, if the points are more on closing, those points should be treated as added.

Here are formulas for both:

Removed points: This formula can work slow or fail to work in the larger account. It iterates through all issues for any sprint.

CASE WHEN
    [Sprint].Currentmember.Level.name = "Sprint"
THEN
  NonZero(SUM(
      Descendants([Issue].Currentmember, [Issue].[Issue]),
     CASE WHEN
     -- points reduced during sprint
     (([Measures].[Story Points history],
      [Time].CurrentHierarchy.Levels('Day').DateMember(
        DateAddDays([Sprint].CurrentMember.get('Start date'),1))
      )
      -
      [Measures].[Sprint Story Points at closing]) > 0
     THEN
      ([Measures].[Story Points history],
      [Time].CurrentHierarchy.Levels('Day').DateMember(
        DateAddDays([Sprint].CurrentMember.get('Start date'),1)))
      -
      [Measures].[Sprint Story Points at closing]
     END
  ))
END

Added points: this formula also might work slow, but it has additional filter by sprint status to improve the performance a bit.

CASE WHEN
    [Sprint].Currentmember.Level.name = "Sprint"
THEN
  NonZero(SUM(
  Filter(
      Descendants([Issue].Currentmember, [Issue].[Issue]),
      CoalesceEmpty([Issue].CurrentMember.Get('Sprint IDs'), "")
         MATCHES ".*"|| Cast([Sprint].CurrentMember.Key as string) ||".*" 
    ),
     CASE WHEN
     -- points reduced during sprint
     (([Measures].[Story Points history],
      [Time].CurrentHierarchy.Levels('Day').DateMember(
        DateAddDays([Sprint].CurrentMember.get('Start date'),1))
      )
      -
      [Measures].[Sprint Story Points at closing]) < 0
     THEN
      Abs(([Measures].[Story Points history],
      [Time].CurrentHierarchy.Levels('Day').DateMember(
        DateAddDays([Sprint].CurrentMember.get('Start date'),1)))
      -
      [Measures].[Sprint Story Points at closing])
     END
  ))
END

Daina / support@eazybi.com