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

gerda.grantina · April 13, 2021, 10:34am

Hi @pmatolikov,
Welcome to the eazyBI community!

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