# Filter your eazyBI for last 5 closed sprints **URL:** \ **Category:** Tips & Tricks\ **Tags:** sprints\ **Created:** [April 29, 2019, 1:01pm UTC](https://community.eazybi.com/t/filter-your-eazybi-for-last-5-closed-sprints/2099 "2019-04-29T13:01:14Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\ **Post date:** [April 29, 2019, 1:01pm UTC](https://community.eazybi.com/t/filter-your-eazybi-for-last-5-closed-sprints/2099/1 "2019-04-29T13:01:15Z")
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](https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Orderandfilterrowsbymeasurevalues) 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 ---
**Author:** ![Bryan\_Rosenthal](https://community.eazybi.com/user_avatar/community.eazybi.com/bryan_rosenthal/32/2365_2.png) [@Bryan\_Rosenthal](https://community.eazybi.com/u/Bryan_Rosenthal)\ **Post date:** [February 5, 2020, 11:31pm UTC](https://community.eazybi.com/t/filter-your-eazybi-for-last-5-closed-sprints/2099/2 "2020-02-05T23:31:16Z")
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! ---
**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\ **Post date:** [February 6, 2020, 6:36am UTC](https://community.eazybi.com/t/filter-your-eazybi-for-last-5-closed-sprints/2099/3 "2020-02-06T06:36:45Z")
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 ---
**Author:** ![Bryan\_Rosenthal](https://community.eazybi.com/user_avatar/community.eazybi.com/bryan_rosenthal/32/2365_2.png) [@Bryan\_Rosenthal](https://community.eazybi.com/u/Bryan_Rosenthal)\ **Post date:** [February 7, 2020, 9:38pm UTC](https://community.eazybi.com/t/filter-your-eazybi-for-last-5-closed-sprints/2099/4 "2020-02-07T21:38:47Z")
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](https://community.eazybi.com/uploads/default/original/2X/3/37bc67d2d4a36d4d377d2ed331dcf9bfeaa0edb8.png) ---
**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\ **Post date:** [February 17, 2020, 8:49am UTC](https://community.eazybi.com/t/filter-your-eazybi-for-last-5-closed-sprints/2099/5 "2020-02-17T08:49:33Z")
@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 ---
**Author:** ![Bryan\_Rosenthal](https://community.eazybi.com/user_avatar/community.eazybi.com/bryan_rosenthal/32/2365_2.png) [@Bryan\_Rosenthal](https://community.eazybi.com/u/Bryan_Rosenthal)\ **Post date:** [March 3, 2020, 4:34pm UTC](https://community.eazybi.com/t/filter-your-eazybi-for-last-5-closed-sprints/2099/6 "2020-03-03T16:34:54Z")
Worked great! Thank you @martins.vanags!