Rolling Prediction with Rate Change

Questions & Answers
1

Hello,

I’ve been trying to create a chart that will predict when we complete a project. I’ve used the Rolling Prediction calculated member from the sample Project Prediction Report, using the avg of the last 4 weeks of velocity, which has worked great!

I’d like to change the rate of progress (i.e. slope of the prediction line) mid-project, so I’ve appended a 2nd WHEN argument below the first, and updated the time boundaries, see below. When I do this, on the day of the rate change, the expected tickets closed jumps up by a lot, but every subsequent day, increases by the 20% I expected (please see the screen shot, and the highlighted unit increase from Sep 28-29 compared to daily increases on other days). Does anyone know why this is happening?

Thanks in advance for any thoughts/suggestions

Screen Shot 2021-04-26 at 2.39.24 PM
Screen Shot 2021-04-26 at 2.39.24 PM368×1698 54.6 KB 

--annotations.group=Predicted by issues
Case
when
  DateInPeriod(
    'Today',[Time].CurrentHierarchyMember)
then
   NonZero((
    [Measures].[Est Days Resolved],
    [Time].CurrentHierarchy.DefaultMember))
When
 DateBetween(
    [Time].CurrentHierarchymember.StartDate,
    'Today',
    -- predicted date by rolling period
    'Sep 28 2021'
    )
  Then
  -- current progress
    NonZero((
    [Measures].[Est Days Resolved],
    [Time].CurrentHierarchy.DefaultMember))
     +
   -- add pace for each day in future
    (AVG(
    {[Time.Weekly].[Week].CurrentDateMember.Lag(4): -- should match the weeks in Rolling predicted 
date measure
     [Time.Weekly].[Week].CurrentDateMember.PrevMember},
    CoalesceEmpty(
    ([Measures].[Est Days Resolved],
     [Time].DefaultMember),
     0))
     -- workdays per week
     / 5 )
   -- axpand one of those two lines for additional scenarious
   -- * 1.10 -- for best case 
   -- * 0.80 -- for worse case
    *
    DateDiffWorkDays('Tomorrow',[Time].CurrentHierarchyMember.NextStartDate)
 
When
 DateBetween(
    [Time].CurrentHierarchymember.StartDate,
    'Sep 29 2021',
    -- predicted date by rolling period
    'Jun 30 2022'
    )
  Then
  -- current progress
    NonZero((
    [Measures].[Est Days Resolved],
    [Time].CurrentHierarchy.DefaultMember))
    +
   -- add pace for each day in future
    (AVG(
    {[Time.Weekly].[Week].CurrentDateMember.Lag(4): -- should match the weeks in Rolling predicted 
date measure
     [Time.Weekly].[Week].CurrentDateMember.PrevMember},
    CoalesceEmpty(
    ([Measures].[Est Days Resolved],
     [Time].DefaultMember),
     0))
     -- workdays per week
     / 5 )
   -- axpand one of those two lines for additional scenarious
   * 1.2 -- for best case 
   -- * 0.80 -- for worse case
    *
    DateDiffWorkDays('Tomorrow',[Time].CurrentHierarchyMember.NextStartDate)


End
2

You are using our example formula for rolling prediction updated to your needs. You almost get it done as needed.

The second part uses a calculation when the increase starts from today (tomorrow). You would like to start the increase from Sep 29 only, though. You would like to build an increase on top of regular rolling predictions on this date as well.

I would suggest calculating prediction for entire period based on the rolling prediction and then add increase only (0.2 instead of 1.2) starting with Sep 29.

Here is an example formula:

Case when
  DateInPeriod(
    'Today',[Time].CurrentHierarchyMember)
then
   NonZero((
    [Measures].[Est Days Resolved],
    [Time].CurrentHierarchy.DefaultMember))
When
 DateBetween(
    [Time].CurrentHierarchymember.StartDate,
    'Today',
    -- predicted date by rolling period
    'Jun 30 2022' 
    )
Then
-- current progress
  NonZero((
  [Measures].[Est Days Resolved],
  [Time].CurrentHierarchy.DefaultMember))
   +
 -- add pace for each day in future
  (AVG(
  {[Time.Weekly].[Week].CurrentDateMember.Lag(4): -- should match the weeks in Rolling predicted date measure
   [Time.Weekly].[Week].CurrentDateMember.PrevMember},
  CoalesceEmpty(
  ([Measures].[Est Days Resolved],
   [Time].DefaultMember),
   0))
   -- workdays per week
   / 5 )
  *
  DateDiffWorkDays('Tomorrow',[Time].CurrentHierarchyMember.NextStartDate)
END
+
-- from this date add additional increase
CASE When
 DateBetween(
    [Time].CurrentHierarchymember.StartDate,
    'Sep 29 2021',
    -- predicted date by rolling period
    'Jun 30 2022'
    )
  Then
   -- add pace for each day in future
    (AVG(
    {[Time.Weekly].[Week].CurrentDateMember.Lag(4): -- should match the weeks in Rolling predicted date measure
     [Time.Weekly].[Week].CurrentDateMember.PrevMember},
    CoalesceEmpty(
    ([Measures].[Est Days Resolved],
     [Time].DefaultMember),
     0))
     -- workdays per week
     / 5 )
   * 0.2 -- add only the increase in pace
    *
    DateDiffWorkDays('Sep 29 2021',[Time].CurrentHierarchyMember.NextStartDate)
End

Daina / support@eazybi.com

1 Like