Forecast report


#1

Hello
I would need to make a forecast report showing the distribution of estimated hours by months (average of hours remaining between the number of months of duration planned for the task) and the hours consumed per month up to the current date.
Can someone help me with this?

The report would be somewhat similar to this:


#2

Hi @adllanog,

In eazyBI you may create a new calculated measure to assess how much should be done each month based on currently remaining estimate. The mathematics beneath calculation is the following:
(estimated hours - consumed hours) / count of remaining months.

This calculation is complex because each issue should be processed individually and a method how to count remaining months differs whether start date is in past or future; for the latter, should use current date for calculation.

Sum(
  --go through all "started" issues in period with End date in future
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    DateBeforePeriodEnd([Measures].[Issue Start Date],[Time].CurrentHierarchy) AND
    DateBetween([Time].CurrentHierarchyMember.StartDate,
      'today',
      [Measures].[Issue End Date]) ),
  --for each issue get remainin estimate
  ( ([Measures].[Original estimated hours],[Time].CurrentHierarchy.DefaultMember)
    - ([Measures].[Hours spent],[Time].CurrentHierarchy.DefaultMember) )
  --and divide by remaining months
  /
  CASE WHEN --validate if Start date is in future
    DateCompare([Measures].[Issue Start Date], 'next month') < 0
  THEN --(1) when Start date in current or past period then calculate remaining month from today
    CASE WHEN --the same year
      Year(DateParse([Measures].[Issue End Date]))
        = Year(DateParse('today'))
    THEN
      Month(DateParse([Measures].[Issue End Date]))
        - Month(DateParse('today'))
    ELSE --different years
      ( Year(DateParse([Measures].[Issue End Date]))
          - Year(DateParse('today')) )*12
       +
       IIf(
         Month(DateParse([Measures].[Issue End Date]))
           > Month(DateParse('today')),
         --more than full year
         Month(DateParse([Measures].[Issue End Date]))
           - Month(DateParse('today')) - 1,
         --less than full year
         Month(DateParse([Measures].[Issue End Date]))
           - Month(DateParse('today'))
       ) 
    END
  ELSE --(2) when Start date in future period, then calculate remaining months from Start date
    CASE WHEN --the same year
      Year(DateParse([Measures].[Issue End Date]))
        = Year(DateParse([Measures].[Issue Start Date]))
    THEN
      Month(DateParse([Measures].[Issue End Date]))
        - Month(DateParse([Measures].[Issue Start Date])) + 1
    ELSE --different years
      ( Year(DateParse([Measures].[Issue End Date]))
          - Year(DateParse([Measures].[Issue Start Date])) ) * 12
       +
       IIf(
         Month(DateParse([Measures].[Issue End Date]))
           > Month(DateParse([Measures].[Issue Start Date])),
         --more than full year
         Month(DateParse([Measures].[Issue End Date]))
           - Month(DateParse([Measures].[Issue Start Date])),
         --less than full year
         Month(DateParse([Measures].[Issue End Date]))
           - Month(DateParse([Measures].[Issue Start Date])) + 1
       ) 
    END
  END
)

Note that calculated measure should be defined in Measures. More information on calculated measures and user functions you will find in the documentation:
https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-members

Best,
Zane / suppor@eazyBI.