How to ignore some Transition status from the Query

Questions & Answers
1

I would like to write the query to aggregate not completed story points and new story points that comes from previous sprint in order to calculate sprint story points committed Except those which are comes from “Ready to Release”, and “Ready to merge state” in previous sprint. I am new with EazyBi, can you please help me.

2

Hi @Behzad_Nazarbakhsh ,
​
At the closure of the sprint, the issues not closed are transferred to a new sprint. However, there is a sprint scope measure “Sprint story points at closing” that allows finding the number of story points brought further.
​That measure also relates to the Transition status dimension. Therefore, it is possible to find the number of story points at the specific status at the end of a specific sprint.
​
​Since you have just two specific statuses to exclude, it might be faster to aggregate just these two statuses than all the others.
​I suggest creating a calculated member in the Transition status dimension named “excepted statuses” with the following expression.
​

Aggregate({
[Transition Status].[Ready to Release],
[Transition Status].[Ready to merge state]})

​
​You might then find the number of story points coming from a specific sprint in other statuses except listed with a following expression.
​

[Measures].[Sprint Story Points at closing]
-
([Measures].[Sprint Story Points at closing],
 [Transition Status].[excepted statuses])

​The next challenge is to find the previous closed sprint for the issue.
That requires retrieving the set of issue sprints and then taking the previous sprint before the current. However, we are only interested in the previous sprint if it brought forward some story points.
The expression for that might be as follows.

Filter(
    Tail(
      Except(
--retrieve previous sprints for the issue
      Intersect(
--all sprints within this board until current sprint     
      {[Sprint].CurrentHierarchyMember.FirstSibling:[Sprint].CurrentHierarchyMember},
--issue sprints     
       [Sprint].[Sprint].GetMembersByKeys([Issue].CurrentHierarchyMember.Get('Sprint IDs'))),
--except current sprint
     [Sprint].CurrentHierarchyMember),
--last previous closed sprint    
    1),
--with story points at closing
   ([Sprint].CurrentHierarchyMember,
   [Measures].[Sprint Story Points at closing])>0)

​
Now it is possible to find the number of story points coming from the previous sprint for the current issue and exclude the excepted statuses.
​
​And now, we only want to perform these calculations for the issues within this sprint.
​For that, we would need to iterate through the Issues dimension to find the issues that have brought points to the current sprint. Since these issues were coming from the previous sprint - they would arrive at the beginning and be committed to the current sprint.
​
​So the full expression to sum up the story points coming into the current sprint from previous sprints with a status not in the list might look as follows.
​

SUM(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Sprint Story Points committed]>0),
CASE WHEN
 Count(
  CACHE(
  Filter(
    Tail( 
      Except(
--retrieve previous sprints for the issue
      Intersect(
--all sprints within this board until current sprint     
      {[Sprint].CurrentHierarchyMember.FirstSibling:[Sprint].CurrentHierarchyMember},
--issue sprints     
       [Sprint].[Sprint].GetMembersByKeys([Issue].CurrentHierarchyMember.Get('Sprint IDs'))),
--except current sprint
     [Sprint].CurrentHierarchyMember),
--last previous closed sprint    
    1),
   ([Sprint].CurrentHierarchyMember,
   [Measures].[Sprint Story Points at closing])>0)))>0
  THEN
 Sum(
  Filter(
    Tail(
      Except(
--retrieve previous sprints for the issue
      Intersect(
--all sprints within this board until current sprint     
      {[Sprint].CurrentHierarchyMember.FirstSibling:[Sprint].CurrentHierarchyMember},
--issue sprints     
       [Sprint].[Sprint].GetMembersByKeys([Issue].CurrentHierarchyMember.Get('Sprint IDs'))),
--except current sprint
     [Sprint].CurrentHierarchyMember),
--last previous closed sprint    
    1),
   ([Sprint].CurrentHierarchyMember,
   [Measures].[Sprint Story Points at closing])>0).item(0),
--the number of story points in acceptable statuses
  [Measures].[Sprint Story Points at closing]
-
([Measures].[Sprint Story Points at closing],
 [Transition Status].[excepted statuses]))
  END
)

You may put this whole expression as a calculated measure and use it in the report.
​You might read more about creating calculated measures here - Calculated measures and members.
​
Regards,
​Oskars / support@eazyBI.com

1 Like