Avg velocity for the last 5 sprints for a specific Team. I want to do a calculated member on the sprint dimension to only show the last 5 closed sprints

Questions & Answers
1

I am trying to create a velocity chart for a particular Team (Team Plum) from multiple teams (Team Oranage, Team Apple, Team Plum, Team Blueberry…etc) within the same project/Board and only want to see the last 5 closed sprints belongs to Team Plum. what will the query be? can any one help me with the solution that works ?
Our project has about more than 10 boards and each board has multiple teams. I want a rolling avg velocity for the last 5 sprints for a specific Team. I want to do a calculated member on the sprint dimension to only show the last 5 closed sprints.

@ janis.plume
Please help me out with the script/query. I tried with the option you have given/suggested to similar request, but no luck.

3

Hi @Jayachander_Lakavath,

Calculated members within a dimension should generally only be related to the specific dimension.
Therefore, the calculated measure “Last 5 closed sprints from all boards” in the demo report Sprint velocity chart retrieves the last 5 sprints from all boards.

Please define the relation of sprints to teams.
Is the same team present on all sprints within the board, or might the team skip some of the sprints?

If you need to skip some sprints that are not related to the specific team within the same board - you might create a calculated member with a hard-coded team name.

The last 5 closed sprints for team Plum in all boards might look as follows.

Aggregate(
 Generate(
  [Sprint].[Board].Members,
 Tail(
  Order(
   Filter(
    DescendantsSet(
      [Sprint].CurrentHierarchyMember,
      [Sprint].[Sprint]),
--closed sprints
    [Measures].[Sprint closed?] = "Yes"
    AND
--related to the team - there were completed issues - resetting all other dimensions
  DefaultContext(    
    ([Measures].[Sprint issues completed],
     [Team].[Team Plum],
     [Sprint].CurrentMember)
     )>0
    ), 
  [Sprint].Currentmember.Get('Start date'), --this is the property by which sprints are sorted
  BASC) 
 ,5)--use other value to show more/less than 5 sprints
 ))

If your team is being dragged around to various boards and their last 5 sprints might come from 2-3 different boards, you might move the Order() and Tail() functions to a slightly higher level of construction within the expression.

The expression for the last 5 closed sprints for team Plum from any board might be as follows.

Aggregate(
 Tail(
  Order(
   Generate(
--retain only boards relevant to team
    Filter(
     [Sprint].[Board].Members,
     DefaultContext(
     ([Measures].[Sprint issues completed],
      [Team].[Team Plum],
      [Sprint].CurrentMember))>0
     ),
--create set of sprints from filtered boards
   Filter(
    DescendantsSet(
      [Sprint].CurrentHierarchyMember,
      [Sprint].[Sprint]),
--closed sprints
    [Measures].[Sprint closed?] = "Yes"
    AND
--related to the team
     DefaultContext(
     ([Measures].[Sprint issues completed],
      [Team].[Team Plum],
      [Sprint].CurrentMember)
   )>0)), 
  [Sprint].Currentmember.Get('Start date'), --this is the property by which sprints are sorted
  BASC) 
 ,5)--use other value to show more/less than 5 sprints
 )

Still, the Team name should be hard-coded within the calculated member.

Regards,
Oskars / support@eazyBI.com