Calculation of required bug outflow based on inflow prediction and limit of amount of opened for milestone

Hello there!
I created a chart with Bug inflow, outflow and currently opened bugs (weekly).
I also added linear trends for each metric.

My intention is to create measures to calculate and set:

  1. To set milestone and limit of amount of opened for this milestone
  2. To calculate needed outflow (which will be based on currently opened bugs, inflow prediction and limit of amount of opened for milestone) for upcomming time periods (weekly in the future)

I assume for Inflow prediction could be used linear trend for inflow (but maybe somebody can propose better way).
I assume calculation should be based on some ideal outflow and take into account possible changes in inflow.

Simple example to describe my idea:
Case #1 (simple):

  1. We have 100 opened bugs and all testing activities are finished (inflow = 0)
  2. We need to fix it till milestone which will happened in 10 weeks.
    Decision: we need fix 100/10=10 bugs per week

Case #2 (my):

  1. We have 100 opened bugs
  2. We have inflow=10 bugs per week which grows (+2 bugs per week)
  3. We need to fix it till milestone which will happened in 10 weeks.
    Decision:
  4. Find linear trend value for inflow for next week as a prediction
  5. Add this inflo to ideal outflow for currently opened bugs which is equal 100/10=10 bugs per week
  6. So outflow for next week should be (10+2)+10 = 22

But how calculate it for several weeks in the future and how to do this with eazyBI, this is a kind of unknown and very hard task for me.

I’ll be appreciated for any help!

Hi @pmatolikov,
Welcome to the eazyBI community! :wave:

From your description, it sounds that you are searching for the guideline (outflow) calculation.
1.You need to have start and end dates for your report (your milestones), in my example I will add manual dates, but, those could be also dates from sprint/version or others. Here is also an example of how to use Project properties for the calculation as start/end dates: Estimated Burndown of Tasks - #3 by zane.baranovska
2.To filter only needed Time period create a measure “Time between milestones” that would mark Time periods (months, weeks, days) between milestones start and end dates. With manually added dates it would be like this:

CASE WHEN
  DateBetween([Time.Weekly].CurrentHierarchyMember.StartDate,
  DateAddDays('Apr 3 2017', -1),
  'Aug 1 2017')
THEN 1
END

3.Create measure “Issue Guideline”. Replace sprint dates with project dates. And for the total scope of estimated hours, you may select the Remaining estimated hours at the first date of the project. The formula might look like this:

CASE WHEN -- period between milestones
  DateBetween([Time].CurrentHierarchyMember.StartDate,
    'Apr 3 2017',
    'Aug 1 2017')
  OR 
  DateInPeriod(
    'Apr 3 2017',
    [Time].CurrentHierarchyMember
  )
THEN
  --Open issues when is the first milestone
  ([Measures].[Open issues],
  [Time].CurrentHierarchy.Levels('Day').DateMember(
    'Apr 3 2017')) *
  --multiplied by remaining days in between milestones
  (
    DateDiffWorkdays(
      'Apr 3 2017',
      'Aug 1 2017')
      -
    DateDiffWorkdays(
      'Apr 3 2017',
      [Time].CurrentHierarchyMember.StartDate
    )
  ) /
  --divided by total count of days in period
  DateDiffWorkdays(
    'Apr 3 2017',
    'Aug 1 2017')
END

4.Then you can add to this measure your linear trend measure (use report the specific measure to reference to added trend measure).

CASE WHEN -- period when project is active
  DateBetween([Time].CurrentHierarchyMember.StartDate,
    'Apr 3 2017',
    'Aug 1 2017')
  OR 
  DateInPeriod(
    'Apr 3 2017',
    [Time].CurrentHierarchyMember
  )
THEN
  --remaining estimated huors when project starts
  (([Measures].[Open issues],
  [Time].CurrentHierarchy.Levels('Day').DateMember(
    'Apr 3 2017')) *
  --multiplied by remaining days in project
  (
    DateDiffWorkdays(
      'Apr 3 2017',
      'Aug 1 2017')
      -
    DateDiffWorkdays(
      'Apr 3 2017',
      [Time].CurrentHierarchyMember.StartDate
    )
  ) /
  --divided by total count of days in period
  DateDiffWorkdays(
    'Apr 3 2017',
    'Aug 1 2017'))
    + [Measures].[Linear trend Issues created]
END


Best,
Gerda