Aggregate "n" Future Sprints with multiple boards

Questions & Answers
1

Hello,

I’m trying to create a report that aggregates the number of “Remaining Estimated Hours” planned per Epic in the next 6 sprints. An issue I’m dealing with is that there are sprints for three separate teams in the project, all with different boards, so I’d like to pull the next 6 sprints for each team, rather than just the next upcoming 18 sprints (which may not be the same depending on sprint creation order). The code below is attempting to aggregate the next upcoming 6 sprints per team, but it’s not working as desired. Can anyone make any suggestions on how I could generate a report of remaining estimated hours per epic that are currently planned into the future 6 sprints?

Thanks,
Joe

Aggregate(
Union(
Union(
head(Filter([Sprint].[Sprint].Members,
[Sprint].CurrentMember.Name MATCHES “.Structures.”
AND
NOT [Sprint].CurrentMember.GetBoolean(“Closed”)
),
6),
head(Filter([Sprint].[Sprint].Members,
[Sprint].CurrentMember.Name MATCHES “.Design.”
AND
NOT [Sprint].CurrentMember.GetBoolean(“Closed”)
),
6)
),
head(Filter([Sprint].[Sprint].Members,
[Sprint].CurrentMember.Name MATCHES “.M&P.”
AND
NOT [Sprint].CurrentMember.GetBoolean(“Closed”)
),
6)
)
)

2

Hi,

The following formula filters the future sprints and aggregates them from several boards.

Aggregate(
  {
  Head(
    Filter([Sprint].[D1 board].Children,
    [Sprint].CurrentMember.Name <> "(no sprint)" AND
    NOT [Sprint].CurrentMember.GetBoolean("Closed") AND
    --sprint start date is in future or not set yet
    (IsEmpty([Sprint].CurrentMember.get('Start date')) OR
      DateCompare([Sprint].CurrentMember.get('Start date'),
      "today") > 0)
  ),6      
  ),
  Head (
      Filter([Sprint].[D2 board].Children,
    [Sprint].CurrentMember.Name <> "(no sprint)" AND
    NOT [Sprint].CurrentMember.GetBoolean("Closed") AND
    --sprint start date is in future or not set yet
    (IsEmpty([Sprint].CurrentMember.get('Start date')) OR
      DateCompare([Sprint].CurrentMember.get('Start date'),
      "today") > 0)
  ),
  6
  )
  }
)

Note that in my case I filtered the future sprints from two boards (D1 board and D2 board). The function of Aggregate can recognize the set defined by set expression:

{set1, set2, set3}

Kindly,

Janis, eazyBI support

3

Thank you, this did the trick!
Thanks,
Joe