You would like to have a calculated measure to retrieve velocity in sprints. I used a calculated measure Story Points Velocity 5 Sprints from our demo account for this:
AVG(
Tail(
-- filter last 5 closed sprints for any Sprint selection
-- for an individual sprint it will show it's completed points
Order(Filter(
Generate(
{ [Sprint].CurrentHierarchyMemberā
ChildrenSet([Sprint].CurrentHierarchyMember) },
Descendants(
[Sprint].CurrentHierarchyMember,
[Sprint].[Sprint] )
),
-- only sprints with committed story points are retrieved
[Sprint].CurrentMember.getBoolean('Closed') AND
NOT IsEmpty([Sprint].CurrentMember.get('Complete date')) AND
[Measures].[Sprint Story Points committed] > 0
), [Sprint].CurrentMember.get('Complete date') , BASC
), 5
),
[Measures].[Sprint Story Points completed]
)
Then you can address sprint velocity when calculating prediction:
Here is an example formula to show a predicted line from today till the scope is resolved:
Case
when
DateInPeriod(
'Today',[Time].CurrentHierarchyMember)
then
NonZero((
[Measures].[Story Points resolved],
[Time].CurrentHierarchy.DefaultMember))
When
DateBetween(
[Time].CurrentHierarchymember.StartDate,
'Today',
-- predicted date
DateAddWorkDays("Today",
-- remaining scope
(([Measures].[Story Points due],
[Time].CurrentHierarchy.Defaultmember)
/
-- current Sprint velocity
([Measures].[Story Points velocity 5 Sprints],
[Time].CurrentHierarchy.DefaultMember))
-- workdays per sprint
* 10))
Then
-- current progress
NonZero((
[Measures].[Story Points resolved],
[Time].CurrentHierarchy.DefaultMember))
+
-- add pace for each day in future
(([Measures].[Story Points velocity 5 Sprints],
[Time].CurrentHierarchy.DefaultMember)
-- workdays per sprint
/ 10 )
-- add one of those two lines for additional scenarious
-- * 1.10 -- for best case
-- * 0.90 -- for worst case
*
DateDiffWorkDays('Tomorrow',[Time].CurrentHierarchyMember.NextStartDate)
End
The formula above will plot the line from today till total points are reached, taking into account Sprint length in workdays - 10 working days (update 10 to any other workday length in the formula above).
It also includes two additional scenarios you can enable - the best case and the worst case. Please create separate measures: one for the best case and another one for the worst case and enable the line according to scenario (remove – from the beginning of the line to enable it). You can set % there as well: 1.10 and 0.90 stands for 10%.
Daina / support@eazybi.com