9. Prediction reports

Hi all,
My query is related to prediction, if any one can address the issues with example, it will be helpful.

*Prediction (Best case +10%, Worst Case - 10%, Likely)of Project end date based on the last 4 sprints data Vs remaining estimates for a project ( Currently JIRA has Version report like this.)

Thank you
Channu Muttal

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

HI ,

I do not want a best case or worst case scenario , just a simple calculation on when the project can be ended based on the Story points due and the average velocity . Can you please help me with that ?