Filter your eazyBI for last 5 closed sprints

Tips & Tricks
1

Hi

This is an approach on how to filter the report for the last 5 closed sprints if you have other dimensions in pages which makes the report context.
You could adjust the code to show more/less than 5 and also switch to show first sprints by changing the ordering conditions.

First, you could create a new calculated measure (only to filter last 5 closed sprints sorted by sprint start dates) in your report using this code:

Case when
[Sprint].Currentmember.Level.Name = 'Sprint'
Then
Case when [Sprint].CurrentMember.Name <> '(no sprint)'
Then
NonZero( Rank(
[Sprint].CurrentMember,
Tail( Order(Filter(
[Sprint].[Sprint].Members,
[Measures].[Issues created] >0
AND [Measures].[Sprint closed?]="Yes" -remove line to include all sprints with startdate
), 
[Sprint].Currentmember.Get('Start date'), BASC) --this is the property by which sprints are sorted
,5))) --use other value to show more/less than 5 sprints
    End
    Else
    [Measures].[Issues created] 
    End

Note the comment on Line11 where you can extend the set

As you can see this code would sort them using “Sprint start date” property.
New calculated measure should return value only for last 5 sprints for the given report context.

Next, you could filter your report rows (“Sprint” dimension members) using this new calculated measure from above (filter rows where new measure value is > 0) and also order the report by the same column before you remove it

Later, you could remove this measure from the report.
The filter conditions should remain.

Cheers!
Martins / eazyBI support

2 Likes
2

Hey @martins.vanags!

This is super helpful. How would this work using the sprint member in the rows/pages? I’d like to create some reports that only populates the last 6 closed sprints in order by closed (or open I suppose) date.

Cheers mate!

3

Hy Bryan,

If I understand your question, this is exactly the purpose of the calculated measure using formula above.

Once you have this, you can filter the report to see only last 6 sprints (ordereded by sprint start or end date)

Martins / eazyBI

4

So this is what I currently am working with. I have the correct sprints coming through, I just can not figure out how to order them chronologically by date. I am using this in the sprint member:

Aggregate(
  Filter([Sprint].[Sprint].Members,
    NOT IsEmpty([Sprint].CurrentMember.get('Start date')) AND 
    DateBetween(
      [Sprint].CurrentMember.get('End date'),
      '12 weeks ago',
      'Now') AND
    ([Sprint].CurrentMember.Name MATCHES '^EPSS Sprint.*' OR
    [Sprint].CurrentMember.Name MATCHES '^EPC Sprint.*')
  )
))

Screen Shot 2020-02-07 at 4.30.14 PM
Screen Shot 2020-02-07 at 4.30.14 PM2412Ă—1464 315 KB

5

@Bryan_Rosenthal

Try this code for your calculated member in “Sprint” dimension

Aggregate(
Order(
  Filter([Sprint].[Sprint].Members,
    NOT IsEmpty([Sprint].CurrentMember.get('Start date')) AND 
    DateBetween(
      [Sprint].CurrentMember.get('End date'),
      '12 weeks ago',
      'Now') AND
    ([Sprint].CurrentMember.Name MATCHES '^EPSS Sprint.*' OR
    [Sprint].CurrentMember.Name MATCHES '^EPC Sprint.*')
  ),
  [Sprint].CurrentMember.GetDate('Start date'),
  BASC
)
)

It has an “order” function to sort sprints by their start dates.
But since you have already enabled this member from the dimension, you might need to reset the “Sprint” dimension members through “All hierarchy level members” menu by clicking on the “Sprint” level button before you enable this new calculated member again and expand to see all sprints sorted correctly.
Then it is safe to remove the header line (to show only sprints but hide the aggregate level).
That is possible only from the “table” view.

Martins / eazyBI support

1 Like
6

Worked great! Thank you @martins.vanags!