How to generate future trend/forecast using work done and remaining estimate

Questions & Answers
1

Hello EazyBI folks,

I need your help to build a report for my mgmt., sort of a version report that also plots the trend for future to help predict when the work will end. My team is doing this in excel for now and we want to switch to EazyBI.

I need to plot the:

  1. Work that has already happened till today – I am plotting the “Cumulative days spent”.
  2. The remaining estimate getting reduced over time till today, I am plotting this via “Remaining days history” measure calculated from the “Remaining estimated hours history”
  3. Plot the trend for (1) in the future – red burndown trend line in below image.
  4. Plot the trend for (2) in the future – green burnup trend line in below image.

Something like the image below (please ignore the slope of the green and red trend lines):
The red and green area is plotted by EazyBI below and I added the lines on the right to show what I am imagining:

image
image1233×355 48 KB

I’m a like a week old user for eazyBI and need your help :blush:

I saw two reports on the demo account that may be similar, but I am unable to understand those clearly:

Some background on our Jira configuration:

Our Jira project is a Kanban based project used by 200+ engineers (we don’t use sprints, sorry mgmt. decision). To count velocity, we also don’t work on issue closure or story points closure per se, but rather on days spent and days remaining, because our issues tend to vary a lot in efforts (as low as 2 days or high as few months).

How can I accomplish this report:

  • From what I understand, to plot the trends for future, I will need to calculate some sort of velocity. I spoke to a few folks around me and received two different suggestions - Use the average work done per week from the start of the project till now as velocity, and another being – use the average work done in the last 4 weeks as velocity. I am leaning towards using the last 4 week average as the velocity.
  • How do I calculate the “Remaining estimate velocity for last 4 weeks” and “Days spent velocity for last 4 weeks”? I see the measure “Story Points Velocity 5 Sprints” in demo account but I don’t understand MDX enough to convert this measure to a work estimate or days spent based measure.
  • Once the velocities measure are defined, how do I use those measures with the “Cumulative days spent” and “Remaining days history” and get the trend prediction line for the future?

Please help!

Thank you so much!

Regards,

Vikrant

2

Hi @nec2152 ,

I am glad you had a look at what the eazyBI Demo account has to offer. The report - Version Story Points Burnup and Burndown - Issues - Jira Demo - eazyBI could be the one that you are looking for. You can build your report on its basis, replacing Story point information with estimates and work logged. The “Story Points history” can be replaced with the “Remaining estimated hours history” or, in your case, “Remaining days history” and the “Cumulative Story points resolved” with “Cumulative days spent”.

Next, to calculate the version burn-up and burn-down, you can use the calculations that are already in the report mentioned above, and update the formulas, to change their scope. For example, the formula for the “Version burn-down” below:

CASE WHEN
  NOT isEmpty([Measures].[Version release date])
  Then
  Case 
  When
  -- start with total points
    DateInPeriod([Measures].[Version report start date],
    [Time].CurrentHierarchyMember)
  Then
   NonZero(([Measures].[Story Points created],
    [Time].CurrentHierarchy.Defaultmember))
  When
-- apply for version planed period
    DateBetween(
      [Time].CurrentHierarchyMember.StartDate,
      [Measures].[Version report start date],
      [Measures].[Version release date]
    )
  Then
  -- total points to burn
   ([Measures].[Story Points created],
    [Time].CurrentHierarchy.Defaultmember)
    /
    -- total days in cycle
    DateDiffDays(
      [Measures].[Version report start date],
      [Measures].[Version release date]
    )
    *
    -- remaining days
    DateDiffDays(
      [Time].CurrentHierarchyMember.NextStartDate,
      DateAddDays([Measures].[Version release date],1)
    )
  End
END

The comments in the formula behind double hyphens describe each section. It calculates the number of days between the version start and end dates. The total number of story points in the version is divided by the number of days in the version. Finally, that is multiplied by the number of days still left in the version. In your case, I would recommend using the “Original estimated hours” or the calculation for days in the burn-down. See the suggested formula below:

CASE WHEN
  NOT isEmpty([Measures].[Version release date])
  Then
  Case 
  When
  -- start with total points
    DateInPeriod([Measures].[Version report start date],
    [Time].CurrentHierarchyMember)
  Then
   NonZero(([Measures].[Original estimated hours],
    [Time].CurrentHierarchy.Defaultmember))
  When
-- apply for version planed period
    DateBetween(
      [Time].CurrentHierarchyMember.StartDate,
      [Measures].[Version report start date],
      [Measures].[Version release date]
    )
  Then
  -- total points to burn
   ([Measures].[Original estimated hours],
    [Time].CurrentHierarchy.Defaultmember)
    /
    -- total days in cycle
    DateDiffDays(
      [Measures].[Version report start date],
      [Measures].[Version release date]
    )
    *
    -- remaining days
    DateDiffDays(
      [Time].CurrentHierarchyMember.NextStartDate,
      DateAddDays([Measures].[Version release date],1)
    )
  End
END

Try to update the burn-up formula yourself, and let me know if you get stuck or have any questions. See more details on exporting the Demo account report definition and importing it in your eazyBI Create reports - eazyBI.

Best,
Roberts // support@eazybi.com

1 Like
3

Hi guys.

How do I create a calculated field with the following rule?

if ‘hours spent’ is filled → guide line = hours remaining - hours spent

else → guide line = remaining hours - average of hours spent

Thanks.