Return estimated hours for issues with due date in past

Questions & Answers
1

eazyBIscreenshot
eazyBIscreenshot.jpg794×672 71.6 KB

I have a report (screenshot above) where I compare estimated hours with sub-tasks, grouped by Epic, to the actual hours spent so far on those same issues/sub-tasks. I am trying to create a third column which shows how many hours should have been spent based on a custom “Issue due date” property. So basically, I would like this column to return estimated hours if the user story/task/etc. has a due date that occurred any time in the past.

If it matters, the hierarchy we are using is:
Epic
-User Story
–Task

I apologize for not having some of my own code to show, but even with a bit of a coding background I have been struggling to use eazyBI formulas. I suspect it’s simple and I’m just not understanding some simple concept, but none of my formulas seem to work.

I appreciate any help that can be provided.

2

After a lot of trial and error, I’m almost there. The only problem I’m having now is that data is not aggregating at the epic level. I have tried aggregating Descendants(), ChildrenSet(), and CascadingChildrenSet() with no luck. See the screenshot below and the yellow highlighted cell for reference. The yellow cell should read 192 as it aggregates the child cells.

eazyBIscreenshot2
eazyBIscreenshot2.jpg684×838 91.8 KB

Any idea what I may be doing wrong with aggregation at the Epic level?

3

Hi @Richard

Sorry for the late reply.

You could try to create a calculated measure with the formula below:

Sum(
  Filter(
    Descendants(
      [Issue].CurrentMember,[Issue].[Issue]
    ),
    DateCompare(
      [Measures].[Issue due date],
      [Time].[Day].DateMember('Today').StartDate
    ) < 0
    AND
    [Measures].[Issue resolution] <> 'Done'
  ),
  [Measures].[Original estimated hours with sub-tasks]
)

This will sum up the values to the Epic level. The condition on the issue resolution will exclude the issues that are resolved.

You can have a look at the eazyBI documentation page for more information on the functions used in the calculated measure - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/filter.

Best,
Roberts // eazyBI support

4

Hi @Richard,
Could you share the formula that you created to get this “scheduled completion hours”?
Thanks
Best Regards