How to get count of completed items with Time for a time period

Questions & Answers
1

Hi, I am trying to get a count for items with the following characteristics

  • Completed within a specific period of time e.g. last 90 days, last 6 months, last 12 months
  • The items have time spent against them

I have created the following items:

  • Time dimension calculated member using
    Aggregate
    (
    [Time].[Day].DateMembersBetween(‘90 Days ago’, ‘today’)
    )
  • Report specific calculated member formula
    Sum(
    {
    [Status].[Done],
    [Status].[Cancelled]
    },
    [Measures].[Issues with hours spent]
    )

The problem is that the calculated member formula does not include in the results items closed in the time period, with time entries outside the time period. e.g if the total completed items in the period is 34, and the items with time entries is 31, the report show 27 for the items with time.
Looking at the 4 missing items, they are issues with time entries that have dates outside the time period. What do I need to change to include those missing items?

2

@carlos.moran

Try this formula for your calcualted measure:

Sum(
Filter(
DescendantsSet([Issue].CurrentHierarchyMember,[Issue].[Issue]),
DateBetween([Measures].[Issue resolution date],'90 Days ago','today')
),
CASE WHEN
Aggregate(
  {[Status].[Done],[Status].[Cancelled]},
  [Measures].[Hours spent]
)>0
THEN
1
END
)

it will find issues resolved in last 90 days with status DONE or Canceled today if issue have hours spent

Martins / eazyBI

3

Hi @martins.vanags ,

Thank you for your help. I tested your suggestion, and the “issues with time” count shows the correct number of items. Unfortunately, this also affects the “Issues Resolved” count, as this should show ALL issues (with time and without), but only shows the issues with time.

Should I be creating a “calculated measure” instead of a “calculated member”?

Thanks

Carlos

4

@carlos.moran
Yes, that code I shared must be used only when creating new calculated measure

Martins / eazyBI support

5

Hi @martins.vanags,

Thank you for your response and for your help. If I use the code in a “calculated measure” for Time Dimension, it removes the items “Done” or “Cancelled” that do not have time in “hours spent”. I need the calculated measure to provide both issues (with or without time). What do I need to add to formula to include items without time in “hours spent”

Thank you for your help

6

Calculated measures should be created only in “Measures” dimension.

Martins