Workload prediction report

Hi there,

I would like to create a report which predicts future workload of members of my team. We update the estimated hours for each ticket and we set an aspirational completion date. Since in this particular project each issue is on worked on by one specific person throughout its lifecycle (this person is the assignee), I thought that a good way to estimate workload per day would be to calculate
DAILY WORKLOAD PER ISSUE = total estimated hours / number of days until aspirational completion date

I managed to get this calculation to work without any problems. The next step would be to associate each DAILY WORKLOAD PER ISSUE with each working day until the aspirational completion date. The end goal is to display this information as a timeline chart with days along the x-axis and the daily workload hours on the y-axis.

Each person will be working on multiple issues so the total daily workload may add up to more than 8 hours but this would be fine. Is there a way to associate a numerical calculated value with a range of dates?

Thanks for your help!

1 Like

Yes, you are correct. You would like to create this calculation per issue - calculate a daily workload per issue (for any workday from start date till end date). You can calculate it with MDX calculation. However, the formula might work slow in accounts with a large number of issues. You can consider using calculated JavaScript calculations to import it as a new separate measure during import as well. I a sharing both solutions.

Both examples use default Jira fields Issue created date as a start date, Issue due date as an end date, and Original estimate as total planned amount. Any of them could be substituted with some custom fields either in MDX calculation or in JavaScirpt custom field.

MDX calculation
Here is an example of MDX calculation for this:

-- show for default time periods on workingdays only
([Time].CurrentHierarchyMember.Level.Name = "Day" 
 NOT CoalesceEmpty([Time].CurrentHierarchyMember.Get("Week day name"),"") MATCHES "Saturday|Sunday")
 or [Time].CurrentHierarchyMember.Level.Name MATCHES "Year|Week|Month|Quarter"
 or [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember
  -- check if an issue has start and end date, and it falls into selected period:
  NOT IsEmpty([Measures].[Issue created date])
  NOT IsEmpty([Measures].[Issue due date])
    [Measures].[Issue created date],
    [Measures].[Issue due date])
    [Measures].[Issue created date], 
  -- workload per day = total planned time / workdays in planned period
  -- total planned time
  ([Measures].[Original estimated hours],
  -- workdays in planned period 
    DateWithoutTime([Measures].[Issue created date]),
    DateAddDays(DateWithoutTime([Measures].[Issue due date]),1)
   -- full workdays in period - workdays in weeks, month, etc. 
     TimestampToDate(IIF(DateInPeriod([Measures].[Issue created date], [Time].CurrentHierarchyMember), 
       DateToTimestamp(DateWithoutTime([Measures].[Issue created date])),
     TimestampToDate(IIF(DateInPeriod([Measures].[Issue due date], [Time].CurrentHierarchyMember), 
       DateToTimestamp(DateAddDays(DateWithoutTime([Measures].[Issue due date]),1)),

The formula might work slow in accounts with large issue count.

Calculated JavaScript custom fields
You can consider calculating the same during import with calculated JavaScript custom fields.

In the code shared below, I am using common JavaScript function workdays from our documentation. You would like to use it to eazyBI advanced settings as well.

So, once you have the common function workdays added to eazyBI advanced settings, you can test and use this custom field definition.

name = "Planned estimates"
data_type = "decimal"
measure = true
scale = 4
multiple_dates = true
javascript_code = '''
function dateWithoutTime (date) {
  var d1 = new Date(Date.parse(date));
  return d1;

startDate = dateWithoutTime(issue.fields.created);
endDate =  dateWithoutTime(issue.fields.duedate);
endDate.setDate( endDate.getDate() + 1 ); // add one day for endDate to count it fully in
totalEstimate = issue.fields.timeoriginalestimate / 3600.0;
nonworkdays = [0,6]; // weekends - Saturday and Sunday

// calculate how many weekdays are in period
weekDaysInPeriod = workdays ( startDate , endDate);

if ( weekDaysInPeriod > 0 && totalEstimate) {
  plannedEstimates = [];
  workLoad = Math.round((totalEstimate / weekDaysInPeriod) * 10000) / 10000 ; // round to scale

  // go in cycle through all days in period from start date till end date and add workload for each workday
  dayInPeriod = startDate;
  while (dayInPeriod < endDate) {
    weekOfDay = dayInPeriod.getDay();
    // on weekdays add data entry for each day
    if (nonworkdays.indexOf(weekOfDay) == -1 ) {
    // add data entry for each day - date and day capacity
        plannedEstimates.push(strftime("%Y-%m-%d", dayInPeriod) + "," + workLoad);
      // get next day
      dayInPeriod.setDate( dayInPeriod.getDate() + 1 );
  issue.fields.customfield_plannedestimates = plannedEstimates.join("\n");

Here is a report definition with both approaches. I added measure representing Original estimates hours with due date to see total amount of planned works (not split between dates).

We suggest importing this newly defined custom field as a measure only.

The measure imported with calculated JavaScript might have some rounding problems, though.

Daina /

1 Like