Last Sprint Name, Start Date and End Date

Questions & Answers
, , ,
1

EazyBI Screenshot
EazyBI Screenshot2118×229 20.2 KB

Hello team,
I am using following calculated member to show Last Closed Sprint metrics as shown in the screenshot. e.g - Issues created, Story Points Completed, Completion Rate etc. However, I am not able to display Last Sprint Name, Start Date and End date of the sprint. Can anybody please help?

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)
  )
)
2

Hi @rakeshprusty,

The expression you posted is designed for grouping sprints in the Sprint dimension. When building calcauted measures, you should use a different approach.

For the calculated measure to show the last sprint properties, name name, start date, end date, yuo should adjust the calculation:

  1. Add predefined measure “Sprint issues at closing” as filter criteria, so the calculation would respond to the report context and selected page filters. Please see the ground rules for calcauted measures: Calculated measures and members

  2. Use function ChildrenSet() to iterate through sprints for selected page filter

  3. Use function get() to access the sprint properties like start and end dates. Or function Name to show the sprint name.

  4. Use function Generate() to return values correctly for display. This function also handles use cases when more than one closed sprint matches the same criteria.

The updated expression to show the last closed sprint name might look like this:

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
  ),
  [Sprint].CurrentMember.Name,
  ", "
)

And the expression to get the last closed sprint sprint end date might look like this:

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"),
  ", "
)

Best,
Zane / support@eazyBI.com

3

Thank for the response Zane! I tried your solution. But the output is still blank. Please refer to the screenshot.

EazyBI Screenshot
EazyBI Screenshot1034×387 19.4 KB

4

Provided calculations are built assuming a “Sprint” dimension in the report as a page filter (as the code is taken from the Sprint dimension and applied to some Demo reports).

Anyhow, if the Sprint dimension is not present in the report, then function ChildrenSet() does not work and returns an empty set; therefore, results are blank in your report. use function Members or DescendntSet() instead to iterate through all Sprints.
Your report layout requires other changes for the calcaution. Add back the function Tail() to get the lates sprint for each project and item() to address the specific member from set and read its properties.

CASE WHEN --condition to run code only for projects that have completed sprints with issues in them
  [Measures].[Sprint issues at closing] > 0
THEN
  Tail(
    --set of closed Sprints
    Filter(
      --iterate though all sprints in the Sprint dimension
      [Sprint].[Sprint].Members,
      [Sprint].CurrentMember.GetBoolean("Closed") AND
      --Sprint matches report context: selected values on pages, rows, and columns
      [Measures].[Sprint issues at closing] > 0
    ),1)
  --address the Sprint from set to read its properties like date
  .Item(0).Get('End date')
END
5

Thank you so much @zane.baranovska . The first solution worked. It was an interpretation mistake.

1 Like
6

Hi @zane.baranovska,
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