How to calculate estimate hours vs. actual hours

Dear There,

I’m using below calculation to get the total estimate hours on the issues resolved, which has been working well so far.

Now I need to create another calculation, to get the total number of hours actually spent on the resolved issues. But seems I cannot find the appropriate item in the default Measures.

I tried to use the [Measures].[Hours Spent] – but found some issues with 8 hours estimated, 7 hours logged – the “hours spent” should be 7 hrs, but the calculation returns 1hrs

Please kindly let me know what measure should be used for the calculation of “actual spent hours on resolved issues”… Thanks!

Regards,
Leon Li

== my working calculation for total estimate hours on resolved issues ==

Sum(
Filter(
Descendants([Issue].CurrentMember,
[Issue].[Issue]),
[Measures].[Issues resolved]>0
),
[Measures].[Original estimated hours resolved]
)

eazyBI maps any measure to Time dimension. Measure Hours spent is mapped to the Time when logged hours was set for. This could be a different period than the one when you resolve an issue.

You would like to use some calculation to access total logged time for resolved issues. I can suggest using a similar measure to the one you use for estimated time:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember
        )
    ),
  ([Measures].[Hours spent],
  [Time].CurrentHierarchy.DefaultMember)
)

I have some suggestions for the formula you shared with us as well.
It seems you are using some calculated JavaScript (or some other approach) to import Original estimates hours mapped with resolution time. In this case, you do not need to use the formula with Issue level calculation you shared with us. You can use a measure Original estimated hours resolved directly in the report to get original estimated hours mapped to resolution time. The formula you are using is required if you want to override mapping by time for a default measure Original estimated hours.

Daina / support@eazybi.com