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

**URL:** <https://community.eazybi.com/t/how-to-get-count-of-completed-items-with-time-for-a-time-period/10118>\
**Category:** Questions & Answers\
**Created:** [July 21, 2022, 4:12pm UTC](https://community.eazybi.com/t/how-to-get-count-of-completed-items-with-time-for-a-time-period/10118 "2022-07-21T16:12:05Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![carlos.moran](https://community.eazybi.com/letter_avatar_proxy/v4/letter/c/f14d63/32.png) [@carlos.moran](https://community.eazybi.com/u/carlos.moran)\
**Post date:** [July 21, 2022, 4:12pm UTC](https://community.eazybi.com/t/how-to-get-count-of-completed-items-with-time-for-a-time-period/10118/1 "2022-07-21T16:12:05Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\
**Post date:** [July 28, 2022, 10:06am UTC](https://community.eazybi.com/t/how-to-get-count-of-completed-items-with-time-for-a-time-period/10118/2 "2022-07-28T10:06:36Z")

</div>

@carlos.moran

Try this formula for your calcualted measure:

```auto
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

---

<div class="post-metadata">

**Author:** ![carlos.moran](https://community.eazybi.com/letter_avatar_proxy/v4/letter/c/f14d63/32.png) [@carlos.moran](https://community.eazybi.com/u/carlos.moran)\
**Post date:** [August 3, 2022, 6:14pm UTC](https://community.eazybi.com/t/how-to-get-count-of-completed-items-with-time-for-a-time-period/10118/3 "2022-08-03T18:14:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\
**Post date:** [August 16, 2022, 6:28am UTC](https://community.eazybi.com/t/how-to-get-count-of-completed-items-with-time-for-a-time-period/10118/4 "2022-08-16T06:28:00Z")

</div>

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

Martins / eazyBI support

---

<div class="post-metadata">

**Author:** ![carlos.moran](https://community.eazybi.com/letter_avatar_proxy/v4/letter/c/f14d63/32.png) [@carlos.moran](https://community.eazybi.com/u/carlos.moran)\
**Post date:** [August 19, 2022, 5:47pm UTC](https://community.eazybi.com/t/how-to-get-count-of-completed-items-with-time-for-a-time-period/10118/5 "2022-08-19T17:47:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\
**Post date:** [August 22, 2022, 7:27am UTC](https://community.eazybi.com/t/how-to-get-count-of-completed-items-with-time-for-a-time-period/10118/6 "2022-08-22T07:27:25Z")

</div>

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

Martins