I need to get the Hours Spent for tickets that were added to the sprint

Questions & Answers
1

Hello,

I have been trying to get the Hours Spent for tickets that were added to the sprint

I have tried this:

([Measures].[Hours spent],
[Sprint Incoming State].[Added]
)

Which I copied the code from [Measures].[Sprint Story Points completed of added]

But when I check individual tickets, i see that it’s not correct.

By the way, my rows have my last 10 sprints.

In the same way I’m trying to get the Hours logged from completed tickets in a sprint:

([Measures].[Hours spent],
  [Transition Status].[Done]
)

which also doesn’t give the correct data.

Can you help?

2

Hi @guillolb,

The calculated measure formula you used to retrieve the logged work in issues that were added to the Sprint after the Sprint start should work as expected. Please share examples where the data is not correct.

The second formula will return the work logged while issues were in the “Done” status in the particular Sprint. It is unlikely that any work was logged in that particular status, so understandably, the result will be incorrect.

The worklog measures work differently from other numeric measures with Sprints. The measure “Hours spent” will return the amount of work logged while an issue was in the particular Sprint, even if it was not completed in it. Please see more details here - Jira Software custom fields.

The two options I can recommend are importing the “Time tracking” measures and using the measure “Hours spent resolved” - Time tracking measures. It will return the total amount of work logged in any Sprint and tie it to the Sprint where the issue was resolved (completed).

If that option doesn’t suit your requirement, define a new calculated measure that will iterate through issues and retrieve the work logged from issues that were completed in the particular Sprint. The formula could look similar to the one below:

CASE WHEN
  [Sprint].CurrentHierarchyMember.Level.Name = "Sprint"
THEN
  Sum(
    Filter(
      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      [Issue].CurrentHierarchyMember.GetNumber('Sprint ID') = [Sprint].CurrentHierarchyMember.GetNumber('URL_ID')
    ),
    CASE WHEN
    -- Check if issue was completed in this sprint
    [Measures].[Sprint issues completed] > 0
    THEN
    -- Get hours spent for completed issues
    [Measures].[Hours spent]
    END
  )
ELSE
  [Measures].[Hours spent]
END

Please look at our documentation page for more information on defining calculated measures - ​Calculated measures and members.

Best,
Roberts // support@eazybi.com