Creating dashboard using eazybi

[shaibaz Qureshi]

If a dev story is planned across multiple months, then we want to split Original estimate across multiple months and fetch the work log for those months. and draft a dashboard.

e.g., for abc-1 if Planned dates are 15th-aug-2024 to 10th-sept-2024 then my original estimate is to be split into the time frame depending on No. of days.

if given OE is 18days then according to the condition the dates should split into 12 work day and 6 work days for Sept and show time spent for august month and for sept month respectively. 

[Zane eazyBI Support]

Hi @shaibaz Qureshi,

You can build the calcaution to distribute the Original estimated hours for the planned period based on workdays in each period.

In the Measures section, define a new calculated measure (https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/calculated-measures).

The calcaution would consist of several logical parts:

1) Check if the issue is planned for the period: the planned start date is before or during the selected period (month), AND the planned end date is after or during the selected period (month).

2) Get the daily pace for each issue: how much of the original estimate should be done on each workday based on planned start and end dates.

3) Calculate the working days for each period. For months when work is planned to start or end, workdays should be calculated according to those dates.

The final expression might look like this:

CASE WHEN -- (1) check if issue is "planned" in selected period
  DateBeforePeriodEnd(
    [Issue].CurrentMember.get('Target start'),
    [Time].CurrentHierarchyMember) AND
  DateCompare(
    [Time].CurrentHierarchyMember.StartDate,
    [Issue].CurrentMember.get('Target end')
  ) < 0 AND
  --issue has original estimated hours
  ([Time].CurrentHierarchy.DefaultMember,
  [Measures].[Original estimated hours]) > 0
THEN -- (2) calcaulte issue daily pace
  [Measures].[Issue Original estimated hours]
  /
  DateDiffWorkdays(
    [Measures].[Issue Target start],
    DateAddDays([Measures].[Issue Target end],1)
  )
  *
  -- (3) claculate planned workdays for each period
  DateDiffWorkdays(
    --start date or beginning of period
    CASE WHEN
      DateCompare(
        [Issue].CurrentMember.get('Target start'),
        [Time].CurrentHierarchyMember.StartDate
      ) > 0
      THEN [Issue].CurrentMember.get('Target start')
      ELSE [Time].CurrentHierarchyMember.StartDate
    END,
    --end date or end of period
    CASE WHEN
      DateCompare(
        [Issue].CurrentMember.get('Target end'),
        [Time].CurrentHierarchyMember.NextStartDate
      ) < 0
      THEN DateAddDays([Issue].CurrentMember.get('Target end'),1)
      ELSE [Time].CurrentHierarchyMember.NextStartDate
    END
  )
END

 This calculation is designed to work with "Target start" and "Target end" dates; if you have another date field for planning, please update the expression.

Expression works when you have selected individual issues on report pages or rows. See picture below.

Best,

Zane / support@eazyBI.com