List of last 6 sprints on a specific Board

Questions & Answers
, , , ,
1

Hello ,

I have a requirement where I want to show the number of bugs created and the number of bugs resolved in each of the sprint belonging to a set of last 6 sprints ( already created this calculated member within the Sprint dimension ) and specific only to particular board . [ I have multiple teams that use different boards within the same project .

Can anyone please help ?

2

Hi @Kunal_Kumar,

If you are looking for only one particular Board sprints, you can hardcode this within the last 6 sprint calculation. For example:

Aggregate(
  Tail(Order(
    Filter(ChildrenSet([Sprint].[BOARD_NAME]),
      [Sprint].CurrentHierarchyMember.getBoolean('Closed') AND
      NOT IsEmpty([Sprint].CurrentHierarchyMember.get('Complete date'))),
    [Sprint].CurrentHierarchyMember.get('Start date'),
    BASC
  ), 6)
)

If you would like to return only the last six sprints from any board, you can look at creating a new hierarchy in the Sprint dimension by importing an additional property to Sprint - Board name only to the last six closed sprints. Please see a similar example here Restrict sprint within board - #2 by lauma.cirule.

Lauma / support@eazybi.com

3

Thank you . But is there a way this can be done without hardcoding. ?

4

@Kunal_Kumar,

One other way is to add Last closed sprints by boards Measure to the report and add column filter to it to be between 1 and 6. And select Sprints on rows and Board in Pages:

Issues_-_eazyBI
Issues_-_eazyBI1644×902 118 KB

Lauma / support@eazybi.com

5

Great . Thank you . Can you please help me with the MDX to create that measure. Not sure how we can do it without hard coding the board name.

6

@Kunal_Kumar,

The Last closed sprints by boards measure is one of the default measures created during Sprint data import. Just in case, here is the formula for it:

CASE
WHEN
  ([Measures].[Issues created],
  [Sprint].CurrentHierarchyMember.Parent) > 0
THEN
  NonZero(
    Rank(
      [Sprint].CurrentHierarchyMember,
      Order(
        Filter(
          [Sprint].CurrentHierarchyMember.Parent.Children,
          NOT isEmpty([Sprint].CurrentHierarchyMember.Get('Start date'))
          AND [Sprint].CurrentHierarchyMember.GetBoolean('Closed')
        ),
      [Sprint].CurrentHierarchyMember.Get('Start date'), DESC)
    )
  )
END

Lauma / support@eazybi.com