How to search for total hours in a sprint between sprint start date and sprint end date

Questions & Answers
1

Hi Guys,
I have the following scenario in my EasyBI, I built a report in my analysis that could create a dimension to fetch total hours worked in a sprint, separated by planned and performed. Follow the generated graph

ScreenHunter%2057
ScreenHunter 57.jpg1862×750 187 KB

I used the operations below, where I called “Predicted” and “Realized”, are hours.
On the Y-axis are all my hours that have been included in the sub-tasks of your User Stories. On Axis X are my sprints.
Measures formula line for the “Forecast”, all hours of the fields
[Measures].[Original estimated hours with sub-tasks] + [Measures].[Original estimated hours]
Fórmula da Measures para o “Realizado”
[Measures].[Hours spent] +
[Measures].[Sub-tasks hours spent]

With the formulas above I get the total number of hours planned and performed,
The problem is in the accumulation of these hours per sprint. I can not get only Sub-tasks that were created during the Sprint creation period.
That is, between Start date Sprint and Star End Sprint dates.
I have already tried the following formula for the “Forecast”.

(count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateBetween(
[Measures].[Issue created date],
[Measures].[Sprint start date],
[Measures].[Sprint end date]
) and
[Measures].[Original estimated hours with sub-tasks] + [Measures].[Original estimated hours]
)
))

However, it continues to accumulate the total number of hours expected in the User Stories Sub-tasks.
How do I get only sub-tasks created during the Sprint period? And not the cumulative all hours of the sub-tasks of all sprints?

How can I find only the hours included in sub-tasks created during the sprint?

2

Hi @rgpnunes,

You are on the right track building the calculated measure. To make it work, you should apply a few improvements: use function Sum() to aggregate estimated hours and iterate through issues in Sub-task hierarchy:

Sum(
  Filter(
    --iterate through all sub-tasks created during Sprint period
    Descendants([Issue.Sub-task].CurrentHierarchyMember,
      [Issue.Sub-task].[Sub-task]),
    DateBetween(
      [Measures].[Issue created date],
      [Measures].[Sprint start date],
      [Measures].[Sprint end date])
  ),
  --sum up Original estimates only of subtasks in Sprint
  [Measures].[Original estimated hours]
)

If sub-tasks themselves are not assigned to any sprint, then in the given calculation you may use a tuple of Original estimated hours and Sprint DefaultMember.

Best,
Zane / support@eazyBI.com