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:
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.