Compare velocity of 2 closed sprints

Hello,
I wrote down a measure that - should - give me a count of the velocity increases in the last 5 sprints, that is the number of times the observed sprint had a higher velocity than the last.
The point is, I came with a question.
The core of my measure is as follows:

Count(
  Filter(
      Tail(
        -- filter last 5 closed sprints in the Board
        Order(
          Filter(
            ChildrenSet([Sprint].CurrentMember),
            -- for closed sprints only
            [Sprint].CurrentMember.getBoolean('Closed')
            AND
            NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))
            AND
            -- only sprints with committed story points are retrieved
            [Measures].[Sprint Story Points committed] > 0 
          ),
          [Sprint].CurrentMember.get('Start date'),
          BASC
        ),5
      ), ([Measures].[Sprint Story Points Completed] > [Measures].[Previous Sprint Velocity])
    )
  )

where the
[Measures].[Previous Sprint Velocity] is
([Measures].[Sprint Story Points completed], [Sprint].CurrentHierarchyMember.PrevMember))

The question is: the previous sprint velocity measure is giving me the right comparison with the sprint in the tail function, or is it just giving me the sprint velocity of the previous sprint regarding the one in the dimensions set?

How can I be sure to compare the sprint I’m looking to in the Filter function to its own predecessor?

regards.

Adding details:

For sprint 2020_15 I have 3 increases as shown by measures on the right (and confirmed by jira reports).

The measure “sprint velocity trend last 5 sprints” should give me a 3 then, but I have a 5.
Any idea why?

I might have isolated the issue: it seems something related to how I filter & order the set of sprints when I head and tail the 5 sprints before the chosen one.
I am using the [all closed sprints] dimension, but maybe that doesn’t fit when I’m looking for board-related sprints, so the differences between the trend and the count may be related to the count considering non board-related sprints.
How can I correctly choose the set?

whats you MDX for “Velocity Trend Indicator”? thats a very useful measure when presenting executive reports.

@jtalarico it’s just up for current velocity > last sprint velocity else down. It’s just a check I built as a middle step towards the complete measure.

so an IF statement with a PRINT? i’m not very good with MDX…could you paste the syntax please?

@jtalarico

CASE WHEN
[Sprint].Currentmember.Level.name = "Sprint"
THEN
CASE WHEN 
[Measures].[Sprint Story Points completed] > [Measures].[Previous Sprint Velocity]
THEN "UP"
WHEN
[Measures].[Sprint Story Points completed] = [Measures].[Previous Sprint Velocity]
THEN "-"
ELSE
"DOWN"
END
END

where [Previous Sprint Velocity] is:

([Measures].[Sprint Story Points completed], [Sprint].CurrentHierarchyMember.PrevMember)

It seems I made it.

CASE WHEN
[Sprint].CurrentHierarchyMember.level.Name = 'Sprint'
THEN
Count(
  Filter(
  Tail(
    -- filter last 5 closed sprints in the Board
    Order(
      Filter(Head(
      Cache(ChildrenSet([Sprint].CurrentHierarchyMember.Parent)),
      Rank([Sprint].CurrentMember,
        Cache(ChildrenSet([Sprint].CurrentHierarchyMember.Parent))
      )
    ),
        -- for closed sprints only
        [Sprint].CurrentMember.getBoolean('Closed')
        AND
        NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))
      ),
      [Sprint].CurrentMember.get('Start date'),
      BASC
    ),5
  ), ([Sprint].CurrentMember, [Measures].[Velocity Trend Indicator]) = 'UP'
)
  )

ELSE
[Measures].[Issues created]
END
1 Like