Active Sprints excluding unwanted specific active sprints

I have to get a list of active sprints excluding some unwanted active sprints. How do I do this?

Hi @nisha,

In the “Sprint” dimension you may create calculated members to filter and group sprints of interest, like Active Sprints, Closed Sprints, particular handpicked sprints, etc.
Check out eazyBI Demo account where are several code examples: Issues - Jira Demo - eazyBI

In your case, you may take the expression of “Active Sprints” as a base for the calculated member and adjust it. Either add filtering criteria to narrow down active sprints to wanted list (for example, wanted sprints has specific boards or pattern in name).

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")
      --you can add more filtering criteria for wanted sprints here
      AND [Sprint].CurrentMember.Name MATCHES ".*Pigeon.*"
      AND ...
    )
)

Or you may add the Except() function to list unwanted sprints.

Aggregate(
  Except(
    --set of all active sprints
    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")
    ),
    --set of unwanted sprints
    {[Sprint].[Panda board].[Pandas - Sprint 9],
    [Sprint].[Fox board].[Fox - Sprint 27]}
  )
)

More details on calculated members to group dimension members and use functions Except() described in the documentation: Calculated members in other dimensions

Best,
Zane / support@eazyBI.com

1 Like

Thanks so much! This was very helpful!

I have a followup issue with finding all issues in a project that is not assigned to planned sprints. Using <‘no name’> did not give me the correct results. Can you please help?