Thanks so much Roberts. This helps a lot!
I do have a couple of follow-on questions…
What would be the best way to limit the number of Sprints that appear in the table once the “group” is selected? Right now, ALL of the Sprints from the beginning of time appears. Let’s say I wanted to limit it to only the last 6, or maybe a set of sprints between two dates?
I know how to do this as a calculated member of the Sprint dimension but it’s not clear to me how to combine that with the group of boards member I created. Do I need to run through every permutation (e.g., Last 6 sprints for Group 1, Lasts 6 sprints for Group 2, Group 1 sprints between June and December, etc.)? That doesn’t seem like the correct/best way.
My motivation for this is that we work in Program Increments (PIs) similar to SAFe. We use fixVersion in Jira to assign PI to a Feature and Epics (not always stories). BUT, there is no association in Jira between fixVersion and sprint so I can’t simply ask eazyBI for all sprints within a given PI (fixVersion). To address this I’ve taken two steps.
First, I’ve created calculated members in Sprint called “PI-1 sprints”, “PI-2 sprints”, etc., as such
Filter(ChildrenSet([Sprint].[All board children]),
NOT IsEmpty([Sprint].CurrentMember.get('End date')) AND
'13 JAN 2020', '08 APR 2020') ),
In this case [All board children] is a calculated member defined as (except for up to 46 boards depending on how I’m grouping the organization):
This appears to work well if I’m creating a report that groups measures across all sprints or all boards. However, in some cases, I want to limit the data to a specific set of sprints (time period) for a specific set of boards. For example, out of our total of 46 boards show me only these 10 boards and the last 12 sprints grouped by PI. The result would look something like this:
To accomplish this I also created calculated measures in the Time dimension for each PI, as such, for PI-12:
[Time].[Day].DateMembersBetween('08 JAN 2020', '07 APR 2020')
Then I created calculated measures for each PI, like this:
[Measures].[Sprint Story Points completed]) /
[Measures].[Sprint Story Points committed])
I think I’m getting the correct, expected results, but validating this is very difficult for a number of reasons.
In any case, being as new to this as I am, I am not super confident in my approach, or my results. Sometimes I get results I can’t explain. For instance, at one point my denominator in the measure above was:
[Measures].[Sprint Story Points committed] +
[Measures].[Sprint Story Points added] -
[Measures].[Sprint Story Points removed]
In certain instances the result of this formula was incorrect. I would be able to see the individual values but the math didn’t add up. Sometimes it did work. I assume it has something to do with trying to aggregate against my Time calculated member, but I can’t be sure. For now, I’ve resorted to using solely points committed in the denominator and I get what seems like reasonable, tough unverified, results.
I would appreciate any feedback you can provide. Thanks again!