Issue status at sprint closing

Questions & Answers
1

I have been trying to get a calculated measure, that counts the number of issues in certain statuses at the sprint closing. I have been using both eazyBI AI and ChatGTP to try to get to the formula but does not seem to work. This is the latest from eazyBI AI:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    (
      [Measures].[Sprint issues at closing],
      [Sprint].CurrentMember
    ) > 0
    AND
    [Sprint].CurrentMember.GetBoolean("Closed")
    AND
    Sum(
      {
        [Transition Status].[Ready for UAT],
        [Transition Status].[In UAT Testing],
        [Transition Status].[Done],
        [Transition Status].[Rejected]
      },
      (
        [Measures].[Issues history],
        [Sprint].CurrentMember
      )
    ) > 0
  ),
  [Measures].[Issues created]
)

For some reason, this seem still to count only Done issues as I get the same numbers as using the Sprint issues completed standard measure. Other attempts have resulted the measure to count the issues but considering their current status, not the status at the sprint closing.

My ultimate goal is to get a measure that counts the number of issues in certain statuses at the sprint closing but only considering those issues that were committed to the sprint. For that I have a separate measure that uses this as one parameter and the sprint issues committed as another.

2

Hello @vpurho ,

To count issues in specific statuses at sprint closing for committed issues, try this simplified formula:

Sum(
  {[Transition Status].[Status1],
  [Transition Status].[Status2]},
  [Measures].[Sprint issues at closing]
)

Replace Status1, Status2, etc., with your desired statuses. This formula counts issues in the specified statuses at sprint closing.

Kindly,
Gerda // support@eazybi.com

3

Thanks. I managed to create (with the help of AI) this kind of function that seems to do what I need. Perhaps too complex but this actually does the trick so that it looks only at those issues that were committed to the sprint and their status at the time of sprint closing.

Sum(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Sprint Issues committed] > 0
    AND
    -- Ensure the issue was in one of the specified statuses at sprint end
    (
      (
        DateCompare(
          ([Measures].[Transition to status last date], [Transition Status].[Ready for UAT]),
          [Sprint].CurrentMember.get('End date')
        ) <= 0
      ) OR
      (
        DateCompare(
          ([Measures].[Transition to status last date], [Transition Status].[In UAT Testing]),
          [Sprint].CurrentMember.get('End date')
        ) <= 0
      ) OR
      (
        DateCompare(
          ([Measures].[Transition to status last date], [Transition Status].[Done]),
          [Sprint].CurrentMember.get('End date')
        ) <= 0
      ) OR
      (
        DateCompare(
          ([Measures].[Transition to status last date], [Transition Status].[Rejected]),
          [Sprint].CurrentMember.get('End date')
        ) <= 0
      )
    )
  ),
  1
)
4

I think I had issues also with the last measure as it did not consider issues that were not transitioned during the sprint at all. So if an issue, for example, remained the whole sprint in status “In UAT Testing”, the measure did not include it. Now I’m using this measure, which seems to work:

Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Sprint issues committed] > 0
),
Sum(
{
[Transition Status].[Ready for UAT],
[Transition Status].[In UAT Testing],
[Transition Status].[Done],
[Transition Status].[Rejected]
},
[Measures].[Sprint issues at closing]
)
)