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

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?

@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

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