Need set of sprints from specific gorup of boards

Hi,
I am trying to create a report that totals up several metrics (e.g., story points committed, story points completed, etc) for a specific set of Sprints from within a group of related Jira Boards and I’m stuck.

Each Scrum Team has their own board. The Scrum Teams are organized into three groups within our organization. So, for example:
Group A
Team A1
Team A2
Team A3
Group B
Team B1
Team B2
Team B3

I want to be able to create reports for each Group separately and, also, combined on one report. I created a calculated member on the Sprint dimension that aggregated the boards of the individual teams, as such:
Aggregate(
{
[Sprint].[Team A1 board],
[Sprint].[Team A2 board],
[Sprint].[Team A3 board]
}
)

But I do not get the results I need. For example, when I add the measure Running Story Points velocity for 5 closed sprints, the values are incorrect. They seem to be averages of a random set of 5 sprints, not the last 5 sprints listed in the rows of the report (see below). In the example the last 5 rows add up to 70 so the running velocity should be 14 but it’s 26.5. What is wrong??

Screen Shot 2020-09-25 at 7.21.46 PM

I’ve seen other answers in the community for filtering based on name and other options, but none really show what I’m looking for. Any help would be greatly appreciated as I am pretty new to MDX and eazyBI

Thank you!

UPDATE:
I’ve made some progress. My intent is to create a calculated measure on the Sprint dimension and then use that dimension for my velocity calculation. I managed to do this (I don’t know if this is ideal or not so any suggestions would be welcomed). However, my velocity calculation isn’t quite correct as it uses the completed points from another team’s board to calculate the velocity for the second team.

Here’s my calculated member, called ‘example’, that works (this is based on the All Closed Sprints calculated member):

Aggregate({
    Order(
          Tail(Filter([Sprint].[Android - Zombies].Children, 
          [Sprint].CurrentMember.getBoolean('Closed') AND 
          NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))),6),
      [Sprint].CurrentMember.get('Start date'), BASC
  ),
  Order(
          Tail(Filter([Sprint].[Android - Pokémon].Children, 
          [Sprint].CurrentMember.getBoolean('Closed') AND 
          NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))),6),
      [Sprint].CurrentMember.get('Start date'), BASC
  )
} )

My calculated measure for velocity (average of last 3 sprints) is:

CASE
WHEN
  [Sprint].CurrentMember is [Sprint].DefaultMember
  OR
  [Sprint].CurrentMember.getBoolean('Closed') AND
  NOT IsEmpty([Sprint].CurrentMember.get('Complete date')) 
  AND [Measures].[Sprint Story Points committed] > 0
THEN
  AVG(Tail(
      Filter(
        Head(
          Cache(ChildrenSet([Sprint].[example])),
          Rank([Sprint].CurrentMember, Cache(ChildrenSet([Sprint].[example]))
          )),
        -- only sprints with committed story points are retrieved
        [Measures].[Sprint Story Points committed] > 0
      ), 3
    ),
    [Measures].[Sprint Story Points completed]
  )
END

When I do this, the velocity calculation is accurate except where the teams ‘overlap’. In the table the two highlighted values are incorrect since they’re based on the Zombies sprints. How do I correct this?

Screen Shot 2020-10-05 at 4.58.15 PM

Also, any better, more efficient, elegant ideas are welcome!

Hi @jrjanis,

Try defining calculated members in the Sprint dimension for each group that aggregates the team Boards, just like you did in the original post. Next, define a new calculated measure, with the formula below:

CASE
WHEN
  [Sprint].CurrentMember is [Sprint].DefaultMember
  OR
  -- for closed sprints only
  [Sprint].CurrentMember.getBoolean('Closed') AND
  NOT IsEmpty([Sprint].CurrentMember.get('Complete date')) AND
  [Measures].[Sprint Story Points committed] > 0
THEN
  AVG(
    Tail(
      -- filter last 5 closed sprints starting from current sprint
      Filter(
        Head(
          Cache(
            Order(
              Filter(VisibleRowsSet(),
                [Sprint].CurrentMember.getBoolean('Closed') AND
                NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))),
              [Sprint].CurrentMember.get('Start date'),
              BASC
            )
          ),
          Rank([Sprint].CurrentMember,
            Cache(
              Order(
                Filter(VisibleRowsSet(),
                  [Sprint].CurrentMember.getBoolean('Closed') AND
                  NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))),
                [Sprint].CurrentMember.get('Start date'),
                BASC
              )
            )
          )
        ),
        -- only sprints with committed story points are retrieved
        [Measures].[Sprint Story Points committed] > 0
      ), 5
    ),
    [Measures].[Sprint Story Points completed]
  )
END

Instead of looking at the Sprint dimension “Sprint” level members from the calculated member “All closed sprints”, it looks at all the Sprints currently on rows. The VisibleRowsSet() function is responsible for this - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/visiblerowsset.

You can now place the Sprint dimension simultaneously on rows and pages, select the Group calculated members in pages, and display the “Sprint” level members on rows. Selecting one or more Groups will adjust the result on rows and the velocity. See a picture of a sample report below:

The eazyBI docs have more information on defining calculated members and measures - https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members.

Best,
Roberts // support@eazybi.com

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

Aggregate({
  Order(
    Filter(ChildrenSet([Sprint].[All board children]),
      [Sprint].CurrentMember.getBoolean('Closed') AND
      NOT IsEmpty([Sprint].CurrentMember.get('End date')) AND
      DateBetween([Sprint].CurrentMember.get('End date'),
        '13 JAN 2020', '08 APR 2020') ),  
    [Sprint].CurrentMember.get('Name'),
    BASC
  )
})

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

Aggregate(
  {
   [Sprint].[Board 1].Children,
   [Sprint].[Board 2].Children,
   [Sprint].[Board 3].Children
   }
)

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:

Aggregate(
  [Time].[Day].DateMembersBetween('08 JAN 2020', '07 APR 2020')
)

Then I created calculated measures for each PI, like this:

CACHE(Aggregate(
  [Time].[PI-12],
  SUM(
    ChildrenSet([Sprint].CurrentHierarchyMember),
    [Measures].[Sprint Story Points completed]) /
  SUM(
    ChildrenSet([Sprint].CurrentHierarchyMember),
    [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!

Regards,
-jj

Hi @jrjanis,

If you intend to keep the Sprint dimension “Sprint” level members on rows, you can add the “Sprint end date” or “Sprint actual end date” properties to the report. Switch to table view and filter the rows by it. See an example below:

Once the filter is set, you can remove the property from the report view and switch to any chart.

See the eazyBI documentation page for more information on filtering rows - https://docs.eazybi.com/eazybijira/analyze-and-visualize/create-reports#Createreports-Orderandfilterrowsbymeasurevalues.

Let me know if this is not what you are looking for.
Best,

Roberts // support@eazybi.com

Hi @roberts.cacus,
I appreciate your response. I don’t think this would give me what I need. I’m looking to group all Sprint 1s, across four or more PIs, together, then all Sprint 2s, then all Sprint 3s, etc. Your suggestion would work for limiting a set of sprints to a common PI. I could filter for sprint end date between the start/end date of a PI. However, to get PI-01.Sprint 1, PI-02.Sprint 1, PI-03.Sprint 1, etc. I wouldn’t be able to use the “between” filter. Here’s a pic of the data table that may help clarify things.
Screen Shot 2020-10-27 at 1.21.49 PM

I have gotten the report to work - or at least generate something that seems reasonably accurate. My current major issues with this report are the following:

  1. it’s incredibly slow to execute. most times it will time out after 60 seconds and I have to refresh. Eventually it will execute and display the chart.
  2. I can’t readily confirm the accuracy of the report. I don’t know for sure that my calculated measures are put together properly and, for various reasons, it is very difficult to verify the results. If I had more confidence in the measures (or if they executed faster), this would be a more realistic task.

Regards,
-jj

P.S.
I am having a different issue with trying to create a measure for specific Sprints. For that one I can’t use your suggestion here because I want the report user to be able to pick a sprint from the Page filter. Perhaps you can take a look at that one? Much appreciated!!

Hi @jrjanis,

Regarding the questions you have about the current report, please export and share the report’s definition https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Exportandimportreportdefinitions. That will give me a better understanding of the current state. Right now, I have trouble wrapping my head around the calculated members.

Regarding the other question you have, I am sure someone from our community or support will eventually pick it up. If not, I will take a look at it once we are done with this question.

Best,
Roberts // support@eazybi.com

Hi Roberts,
Weren’t sure if you meant for me to share it here or in an email, but here it is:

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Avg. Sprint Completion Rate by PI",
     "folder_name": "Story Points",
     "result_view": "bar_chart",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[PI-11 Avg SCR v2]","[Measures].[PI-12 Avg SCR v2]","[Measures].[PI-13 Avg SCR v2]","[Measures].[PI-14 Avg SCR v2]"],"members":[]}]},"rows":{"dimensions":[{"name":"Sprint","selected_set":["[Sprint].[All sprint 1 sprints]","[Sprint].[All sprint 2 sprints]","[Sprint].[All sprint 3 sprints]","[Sprint].[All sprint 4 sprints]","[Sprint].[All sprint 5 sprints]","[Sprint].[All sprint 6 sprints]"],"members":[],"bookmarked_members":[]}],"nonempty_crossjoin":true},"pages":{"dimensions":[]},"options":{"nonempty":"rows"},"view":{"current":"bar_chart","maximized":false,"bar_chart":{"stacked":false,"vertical":true,"swap_axes":false,"data_labels":true,"series_options":{"Story point completion rate":{"type":"column","separateAxis":1,"dataLabelType":"top rotated","color":"#00875A"},"Sprint Story Points committed":{"dataLabelType":"top horizontal"},"Sprint Story Points completed":{"dataLabelType":"top horizontal"}},"y_axis":{"0":{"title":"Story Points"},"1":{"title":"Sprint Completion Rate"}}},"table":{"freeze_header":true}},"calculated_members":[]}
  } ],
  "calculated_members": [{"name":"Sprint Story Points committed","dimension":"Measures","format_string":"#,##0.00","formula":"( [Measures].[Story Points added],\n  [Transition Field].[Sprint status],\n  [Sprint Status].[Active],\n  -- An issue was in a sprint at a sprint start time\n  [Issue Sprint Status Change].[Future =\u003e Active]\n)\n"},{"name":"Sprint Story Points added","dimension":"Measures","format_string":"#,##0.00","formula":"( [Measures].[Story Points added],\n  [Transition Field].[Sprint status],\n  [Sprint Status].[Active],\n  -- An issue was added or created in an active sprint\n  [Issue Sprint Status Change].[(none) =\u003e Active]\n)\n"},{"name":"Sprint Story Points removed","dimension":"Measures","format_string":"#,##0.00","formula":"( [Measures].[Story Points removed],\n  [Transition Field].[Sprint status],\n  [Sprint Status].[Active],\n  -- An issue was removed from an active sprint\n  [Issue Sprint Status Change].[Active =\u003e (none)]\n)\n"},{"name":"Sprint Story Points at closing","dimension":"Measures","format_string":"#,##0.00","formula":"-- Story points from issues when a sprint was closed\n( [Measures].[Story Points added],\n  [Transition Field].[Sprint status],\n  [Sprint Status].[Closed],\n  [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{"name":"Sprint Story Points completed","dimension":"Measures","format_string":"#,##0.00","formula":"(\n  [Measures].[Sprint Story Points at closing],\n  [Transition Status.Category].[Done]\n)\n"},{"name":"All board children","dimension":"Sprint","formula":"Aggregate(\n  {\n   [Sprint].[Android - BBQ].Children,\n   [Sprint].[Android - Legoland Team].Children,\n   [Sprint].[Android - Pokémon].Children,\n   [Sprint].[Android - Zombies].Children,\n   [Sprint].[Android - Minions].Children,\n   [Sprint].[BENEFITS board].Children,\n   [Sprint].[ALEX board].Children,\n   [Sprint].[AMPS board].Children,\n   [Sprint].[APEX].Children,\n   [Sprint].[AWBB board].Children,\n   [Sprint].[ENC board].Children,\n   [Sprint].[GAGA board].Children,\n   [Sprint].[MOMO board].Children,\n   [Sprint].[SEDONA board].Children,\n   [Sprint].[BLUES board].Children,\n   [Sprint].[CBO board].Children,\n   [Sprint].[Voyager Sprint Board].Children,\n   [Sprint].[ENT board].Children,\n   [Sprint].[EXP Sprint Planning board].Children,\n   [Sprint].[GEM board].Children,\n   [Sprint].[Homestead Aloha (702)].Children,\n   [Sprint].[Homestead Keahi].Children,\n   [Sprint].[Homestead Mahalo].Children,\n   [Sprint].[i18n board].Children,\n   [Sprint].[Valor Planning].Children,\n   [Sprint].[Rhapsody Planning].Children,\n   [Sprint].[Tempest Planning].Children,\n   [Sprint].[PRIMO board].Children,\n   [Sprint].[NGM Scrum].Children,\n   [Sprint].[RHYTHM board].Children,\n   [Sprint].[ROADRUNNER board].Children,\n   [Sprint].[WILEE board].Children,\n   [Sprint].[ADAM Scrum by Assignee].Children,\n   [Sprint].[ARMS board].Children,\n   [Sprint].[ASF board].Children,\n   [Sprint].[BI board].Children,\n   [Sprint].[CMS Backlog].Children,\n   [Sprint].[CSI board].Children,\n   [Sprint].[CRM board].Children,\n   [Sprint].[DSS board].Children,\n   [Sprint].[DMAR board].Children,\n   [Sprint].[DP board].Children,\n   [Sprint].[DADC board].Children,\n   [Sprint].[DSEA board].Children,\n   [Sprint].[INFOSEC board].Children,\n   [Sprint].[P13N Core Board].Children,\n   [Sprint].[PS Unfiltered Board].Children,\n   [Sprint].[SBE board].Children\n   }\n)","format_string":""},{"name":"PI-12","dimension":"Time","formula":"Aggregate(\n  [Time].[Day].DateMembersBetween('08 JAN 2020', '07 APR 2020')\n)","format_string":""},{"name":"PI-13","dimension":"Time","formula":"Aggregate(\n  [Time].[Day].DateMembersBetween('08 APR 2020', '07 JUL 2020')\n)","format_string":""},{"name":"All sprint 1 sprints","dimension":"Sprint","formula":"Aggregate({\n  Order(\n    Filter(ChildrenSet([Sprint].[All board children]),\n      [Sprint].CurrentMember.getBoolean('Closed') AND\n      NOT IsEmpty([Sprint].CurrentMember.get('End date')) AND\n      (DateBetween([Sprint].CurrentMember.get('End date'),\n        '21 OCT 2019', '25 OCT 2019') OR -- PI-11\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '20 JAN 2020', '24 JAN 2020') OR -- PI-12\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '20 APR 2020', '24 APR 2020') OR -- PI-13\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '20 JUL 2020', '24 JUL 2020') -- OR -- PI-14\n--        DateBetween([Sprint].CurrentMember.get('End date'),\n--        '13 JAN 2020', '08 APR 2020') -- PI-15\n        )\n    ),\n    [Sprint].CurrentMember.get('End Date'),\n    BASC\n  )\n})","format_string":""},{"name":"All sprint 2 sprints","dimension":"Sprint","formula":"Aggregate({\n  Order(\n    Filter(ChildrenSet([Sprint].[All board children]),\n      [Sprint].CurrentMember.getBoolean('Closed') AND\n      NOT IsEmpty([Sprint].CurrentMember.get('End date')) AND\n      (DateBetween([Sprint].CurrentMember.get('End date'),\n        '04 NOV 2019', '08 NOV 2019') OR -- PI-11\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '03 FEB 2020', '07 FEB 2020') OR -- PI-12\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '04 MAY 2020', '08 MAY 2020') OR -- PI-13\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '03 AUG 2020', '07 AUG 2020') -- OR -- PI-14\n--        DateBetween([Sprint].CurrentMember.get('End date'),\n--        '02 NOV 2020', '06 NOV 2020') -- PI-15\n        )\n    ),\n    [Sprint].CurrentMember.get('End Date'),\n    BASC\n  )\n})","format_string":""},{"name":"All sprint 3 sprints","dimension":"Sprint","formula":"Aggregate({\n  Order(\n    Filter(ChildrenSet([Sprint].[All board children]),\n      [Sprint].CurrentMember.getBoolean('Closed') AND\n      NOT IsEmpty([Sprint].CurrentMember.get('End date')) AND\n      (DateBetween([Sprint].CurrentMember.get('End date'),\n        '18 NOV 2019', '22 NOV 2019') OR -- PI-11\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '17 FEB 2020', '21 FEB 2020') OR -- PI-12\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '18 MAY 2020', '22 MAY 2020') OR -- PI-13\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '17 AUG 2020', '21 AUG 2020') -- OR -- PI-14\n--        DateBetween([Sprint].CurrentMember.get('End date'),\n--        '16 NOV 2020', '20 NOV 2020') -- PI-15\n        )\n    ),\n    [Sprint].CurrentMember.get('End Date'),\n    BASC\n  )\n})","format_string":""},{"name":"All sprint 4 sprints","dimension":"Sprint","formula":"Aggregate({\n  Order(\n    Filter(ChildrenSet([Sprint].[All board children]),\n      [Sprint].CurrentMember.getBoolean('Closed') AND\n      NOT IsEmpty([Sprint].CurrentMember.get('End date')) AND\n      (DateBetween([Sprint].CurrentMember.get('End date'),\n        '02 DEC 2019', '06 DEC 2019') OR -- PI-11\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '02 MAR 2020', '06 MAR 2020') OR -- PI-12\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '01 JUN 2020', '05 JUN 2020') OR -- PI-13\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '31 AUG 2020', '04 SEP 2020') -- OR -- PI-14\n--        DateBetween([Sprint].CurrentMember.get('End date'),\n--        '30 NOV 2020', '04 DEC 2020') -- PI-15\n        )\n    ),\n    [Sprint].CurrentMember.get('End Date'),\n    BASC\n  )\n})","format_string":""},{"name":"All sprint 5 sprints","dimension":"Sprint","formula":"Aggregate({\n  Order(\n    Filter(ChildrenSet([Sprint].[All board children]),\n      [Sprint].CurrentMember.getBoolean('Closed') AND\n      NOT IsEmpty([Sprint].CurrentMember.get('End date')) AND\n      (DateBetween([Sprint].CurrentMember.get('End date'),\n        '16 DEC 2019', '20 DEC 2019') OR -- PI-11\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '16 MAR 2020', '20 MAR 2020') OR -- PI-12\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '15 JUN 2020', '19 JUN 2020') OR -- PI-13\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '14 SEP 2020', '18 SEP 2020') -- OR -- PI-14\n--        DateBetween([Sprint].CurrentMember.get('End date'),\n--        '14 DEC 2020', '18 DEC 2020') -- PI-15\n        )\n    ),\n    [Sprint].CurrentMember.get('End Date'),\n    BASC\n  )\n})","format_string":""},{"name":"All sprint 6 sprints","dimension":"Sprint","formula":"Aggregate({\n  Order(\n    Filter(ChildrenSet([Sprint].[All board children]),\n      [Sprint].CurrentMember.getBoolean('Closed') AND\n      NOT IsEmpty([Sprint].CurrentMember.get('End date')) AND\n      (DateBetween([Sprint].CurrentMember.get('End date'),\n        '30 DEC 2019', '03 JAN 2020') OR -- PI-11\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '30 MAR 2020', '03 APR 2020') OR -- PI-12\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '29 JUN 2020', '03 JUL 2020') OR -- PI-13\n        DateBetween([Sprint].CurrentMember.get('End date'),\n        '28 SEP 2020', '02 OCT 2020') -- OR -- PI-14\n--        DateBetween([Sprint].CurrentMember.get('End date'),\n--        '28 DEC 2020', '03 JAN 2021') -- PI-15\n        )\n    ),\n    [Sprint].CurrentMember.get('Name'),\n    BASC\n  )\n})","format_string":""},{"name":"PI-11","dimension":"Time","formula":"Aggregate(\n  [Time].[Day].DateMembersBetween('09 OCT 2019', '07 JAN 2020')\n)","format_string":""},{"name":"PI-13 Avg SCR v2","dimension":"Measures","formula":"CASE WHEN -- Sprint 1\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 1 sprints]\nTHEN\n  NonZero(\n    AVG(\n      CrossJoin([Sprint].[All sprint 1 sprints], [Time].[PI-13]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 2\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 2 sprints]\nTHEN\n  NonZero(\n    AVG(\n      CrossJoin([Sprint].[All sprint 2 sprints], [Time].[PI-13]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 3\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 3 sprints]\nTHEN\n  NonZero(\n    AVG(\n      CrossJoin([Sprint].[All sprint 3 sprints], [Time].[PI-13]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 4\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 4 sprints]\nTHEN\n  NonZero(\n    AVG(\n      CrossJoin([Sprint].[All sprint 4 sprints], [Time].[PI-13]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 5\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 5 sprints]\nTHEN\n  NonZero(\n    AVG(\n      CrossJoin([Sprint].[All sprint 5 sprints], [Time].[PI-13]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 6\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 6 sprints]\nTHEN\n  NonZero(\n    AVG(\n      CrossJoin([Sprint].[All sprint 6 sprints], [Time].[PI-13]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nEND\nEND\nEND\nEND\nEND\nEND","format_string":"#0.00%"},{"name":"PI-14 Avg SCR v2","dimension":"Measures","formula":"CASE WHEN -- Sprint 1\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 1 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 1 sprints], [Time].[PI-14]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 2\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 2 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 2 sprints], [Time].[PI-14]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 3\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 3 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 3 sprints], [Time].[PI-14]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 4\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 4 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 4 sprints], [Time].[PI-14]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 5\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 5 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 5 sprints], [Time].[PI-14]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 6\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 6 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 6 sprints], [Time].[PI-14]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nEND\nEND\nEND\nEND\nEND\nEND","format_string":"#0.00%"},{"name":"PI-14","dimension":"Time","formula":"Aggregate(\n  [Time].[Day].DateMembersBetween('08 JUL 2020', '06 OCT 2020')\n)","format_string":""},{"name":"PI-11 Avg SCR v2","dimension":"Measures","formula":"CASE WHEN -- Sprint 1\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 1 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 1 sprints], [Time].[PI-11]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 2\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 2 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 2 sprints], [Time].[PI-11]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 3\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 3 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 3 sprints], [Time].[PI-11]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 4\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 4 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 4 sprints], [Time].[PI-11]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 5\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 5 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 5 sprints], [Time].[PI-11]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 6\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 6 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 6 sprints], [Time].[PI-11]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nEND\nEND\nEND\nEND\nEND\nEND","format_string":"#0.00%"},{"name":"PI-12 Avg SCR v2","dimension":"Measures","formula":"CASE WHEN -- Sprint 1\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 1 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 1 sprints], [Time].[PI-12]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 2\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 2 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 2 sprints], [Time].[PI-12]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 3\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 3 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 3 sprints], [Time].[PI-12]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 4\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 4 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 4 sprints], [Time].[PI-12]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 5\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 5 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 5 sprints], [Time].[PI-12]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nELSE CASE WHEN -- Sprint 6\n  [Sprint].CurrentHierarchyMember IS [Sprint].[All sprint 6 sprints]\nTHEN\n  Cache(\n    AVG(\n      CrossJoin([Sprint].[All sprint 6 sprints], [Time].[PI-12]),\n      ([Measures].[Sprint Story Points completed] /\n      ([Measures].[Sprint Story Points committed] +\n       [Measures].[Sprint Story Points added] -\n       [Measures].[Sprint Story Points removed]))\n       )\n    )\nEND\nEND\nEND\nEND\nEND\nEND","format_string":"#0.00%"}]
}

Hi @jrjanis,

I am sorry it took me so long to reply. The report and calculations are very complex, and it took me some time before it clicked. See some suggestion on improving the performance of the report below:

Sprint dimension calculated members
Instead of multiple calculated members in the Sprint dimension, try the additional data import and defining a new hierarchy in the Sprint dimension.

Import the desired group name (Sprint 1, Sprint 2… Sprint 6) for each Sprint with the additional data import as a Sprint dimension “Sprint” level member property. See a picture of the data mapping for a similar example below:


See our documentation page for more details on the additional data mapping - https://docs.eazybi.com/eazybijira/data-import/jira-issues-import/additional-data-import-into-jira-issues-cube.

Once the import of the property is done, define a new hierarchy based on this property:


The result then could look similar to the one below:

Read more on custom hierarchies here - https://docs.eazybi.com/eazybijira/analyze-and-visualize/create-reports#Createreports-AddcustomhierarchiestospecificJiraandConfluencedimensions.

Time dimension calculated members
Another performance hog is the calculated members in the Time dimension that aggregate “Day” level members. I suggest defining a new hierarchy in the Time dimension. A suitable option seems to be a new Fiscal hierarchy. Unfortunately, it will begin on the first day of the month you specify and not a specific date, like in your case. See if this is an option - https://docs.eazybi.com/eazybijira/analyze-and-visualize/create-reports#Createreports-AddcustomhierarchiestoTimedimension.

AVG calculated measures
If both the above options can be applied to your use case, instead of four calculated measures, you can use one:

NonZero(Avg(
  ChildrenSet([Sprint].CurrentMember),
  ([Measures].[Sprint Story Points completed] /
  ([Measures].[Sprint Story Points committed] +
  [Measures].[Sprint Story Points added] -
  [Measures].[Sprint Story Points removed]))
))

The report then could look similar to the one below:

Best,
Roberts // support@eazybi.com