Finding the end date for the active Sprint

Questions & Answers
, , ,
1

I am defining the following calculated member for finding the end date for current active sprint across all the boards in my project :

Aggregate(
Order(
Filter(
[Sprint].[Sprint].Members,
NOT [Sprint].CurrentMember.getBoolean(‘Closed’)
),

– Filtering the Active sprint on the board
[Sprint].CurrentHierarchyMember.get(‘End date’)
))

I am selecting the formatting as mm dd yyyy . However i’m not getting any date in the result rather i’m getting the text “mm dd yyyy”

2

I see you have the correct way to get the sprint, but is it being imported from Jira?

3

AGGREGATE returns a number.
See Sprint timeline in epic - Issues - Jira Demo - eazyBI for multiple sprints start/end dates in a chart.

Hope it helps!
Vasile S.

4

Hi,

The Aggregate function is not needed for this use case.
The following formula finds the active sprint with the latest end date (there can be several across multiple boards):

Order (
Filter(  [Sprint].[Sprint].Members,
  Not [Sprint].CurrentMember.GetBoolean('Closed')),
  [Sprint].CurrentMember.Get('End date'),
  BDESC
  ).Item(0).Get('End date')

Kindly,
Janis eazyBI support

5

Hi @janis.plume,

I want to display End Dates for both current Sprint and last Closed Sprint. Currently I am using this setting.
In Pages, I am using these filters -
Last Closed Sprint:
Aggregate(
Generate(
– get all boards
[Sprint].[Board].Members,
– access all children - sprints from the board and get the last 5 closed
Tail(
Filter(
[Sprint].CurrentMember.Children,
[Sprint].CurrentMember.GetBoolean(“Closed”)),
– 1 last (from Tail) closed sprint from a board
1)
– address the first (0) member from a set
.Item(0)
)
)
In Measures I am using this formula:
Last Sprint End Date:
Generate(
–set of closed Sprints
Filter(
ChildrenSet([Sprint].CurrentHierarchyMember),
[Sprint].CurrentMember.GetBoolean(“Closed”) AND
–Sprint matches report context: selected values on pages, rows, and columns
[Measures].[Sprint issues at closing] > 0
),
Format([Sprint].CurrentMember.Get(‘End date’), “dd mmm yyyy”),
", "
)
This setting is working fine for Last Sprint. However, I am using the below settings for Current Sprint but it’s not working for me.
Current Sprint filter in Pages:
Aggregate(
Filter(
[Sprint].[Sprint].Members,
NOT [Sprint].CurrentMember.getBoolean(‘Closed’)
AND
IIF (IsEmpty([Sprint].CurrentMember.get(‘Status’)),
NOT isEmpty([Sprint].CurrentMember.get(‘Start date’)) ,
[Sprint].CurrentMember.get(‘Status’) = “Active”)
)
)
Current Sprint End Date formula:
Generate(
–set of closed Sprints
Filter(
ChildrenSet([Sprint].CurrentHierarchyMember),
NOT [Sprint].CurrentMember.GetBoolean(“Closed”)
),
Format([Sprint].CurrentMember.Get(‘End date’), “dd mmm yyyy”),
", "
)

Is there anyway I can use a single formula to get the End Dates and I just need to select the right member in Pages? Please advice.

Thanks!
Rakesh

6

Hi Rakesh,

The formula for finding the End date should not need sprint filtering, so it will work for any Sprint member selected in the report pages:

Generate(
  ChildrenSet([Sprint].CurrentHierarchyMember),
  Format([Sprint].CurrentMember.Get("End date"), "dd mmm yyyy"),
", "
)

Kindly,
Janis, eazyBI support

7

Thanks for the reply @janis.plume . I am trying to display different Sprint attributes like Total story points committed, Story points resolved etc. Along with that I want to display Sprint Start Date, End Date and time remaining. I think we need to filter on current sprint. The above formula is not working for either case. Please see attached screenshot for reference.

Screenshot 2024-02-14 142518
Screenshot 2024-02-14 1425182106×308 20.8 KB

8

Hi,

Well, that depends on how you use the sprint filtering. (no board) selection will not show sprint end date. My suggestion assumed you select a specific sprint member in the pages.

Kindly,
Janis, eazyBI support