Filter on Sprint To Show Specific Boards For Sprints Closed Within Previous Month

[cpuglisi]

Hi all!

I’m trying to create a table report that has a calculated measure on the Sprint dimension. It will show only specific boards for Sprints that were opened within the previous month.

This is the code I have so far (defined in a new calculated measure on the Sprint dimension) but I keep getting syntax errors:

Aggregate({
[Sprint].[MCX Agile Team 1 Scrum Board],
[Sprint].[MCX Agile Team 2 Scrum Board],
[Sprint].[FMC Agile Team Scrum Board],
[Sprint].[FMC Screeners Scrum Board]
})
CASE WHEN
Aggregate({
Tail(Order(
Filter([Sprint].[Sprint].Members,
[Measures].[Sprint Closed?] = ‘Yes’ AND
NOT isEmpty([Measures].[Sprint End Date])),
[Sprint].CurrentMember.get(‘End date’), BASC
), 4)
})
END

[zane.baranovska]

Hi @cpuglisi,

When aggregating a group of members, first, you might want to decide which level members (in your case Boards or Sprints) you would like to group. And then write all filtering criteria from that level perspective. For example, to aggregate Sprints, start by filtering Sprint level members by certain rules:

• specific Board (sprint parent) name
• sprint is closed
• sprint end date is in the previous month

The calculation might look like this:

Aggregate(
  --filter sprints
  Filter([Sprint].[Sprint].Members,
  --from specified boards
  [Sprint].CurrentMember.Parent.Name MATCHES "MCX Agile Team 1 Scrum Board|MCX Agile Team 2 Scrum Board|FMC Agile Team Scrum Board|FMC Screeners Scrum Board" AND
  [Measures].[Sprint Closed?] = 'Yes' AND
  --sprint end date in previous month
  DateInPeriod([Measures].[Sprint end date],
    [Time].[Month].CurrentDateMember.PrevMember)
))

More details on how to aggregate members in dimension and regular expressions are here:

• https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members/calculated-members-in-other-dimensions
• https://docs.eazybi.com/eazybi/analyze-and-visualize/regular-expressions

Best,
Zane / support@eazyBI.com

[XavierH]

HI @zane.baranovska , i was trying your solution but i mayke some adjustement to fit my need.

I got several board that are all name with the format "Scrum " & Team_name

I try this syntaxe but i got no result.

 [Sprint].CurrentMember.Parent.Name MATCHES "^Scrum*"

[zane.baranovska]

Hi @XavierH,

If you would like to group sprints with the name pattern starting with “Scrum” you might slightly adjust the expression.
First, check the CurrnetMember name directly, not its Parent.
When checking on name patterns, use the regular expression .* (see examples in Regular expressions in calculated members).

[Sprint].CurrentMember.Name MATCHES "^Scrum.*"

For more complex use cases to group sprints by name patterns, check out other community posts describing how to create a custom hierarchy in the Sprint dimension.

Combining different teams sprints into one

With version 6.1 we added a new to create custom hierarchies in some dimensions, including Sprints. You would like to import custom properties for sprints to represent a potential grouping mechanism. For example, you can import a property cycle for Sprints with additional data import. Then you can use this custom property Cycle to build a new custom hierarchy in Sprint dimension and group sprints by cycles dynamically. Import additional property to Sprint based on Sprint naming pattern XX Spr…

Aggregate Sprint By Name

Hi @Gisela_Nogueira, You can add custom hierarchy that would group sprints by matching string or numeric properties (sprints with the same property value are grouped) in Sprint dimension. First, you might want to import criteria for grouping sprints as a property using additional data import: Additional data import into Jira Issues cube - eazyBI for Jira. For example, import planned start period as string, program increment, or some other attribute indicates which sprints should be grouped. …

Best,
Zane / support@eazyBI.com