Count of 'live' issues in current week/day

Questions & Answers
1

Hello,

In our current system we have a Start Date and End Date for a certain issue type. I want to find out which issues are being worked on over time.
In the rows I have the Issue Type and Time, in the measures i want to count the live issues. An issue is live if if the start date is less than the current date and end date is equal to the current date or in future, or if the start date is the current date.

I was thinking something like this but wasn’t sure how to get the numbers required:

NonZero(
  Count(
    Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
      ([Measures].[Issue Start date] < [Time].CurrentHierarchyMember AND ([Measures].[Issue End date] = [Time].CurrentHierarchyMember OR [Measures].[Issue End date]> [Time].CurrentHierarchyMember)) OR 
      ([Measures].[Issue Start date] = [Time].CurrentHierarchyMember.StartDate)
    ),
    -- counter by measure works as a filter as well:
    ([Measures].[Issues created],
	 [Time].CurrentHierarchy.DefaultMember)
  )
)

Any help much appreciated :slight_smile:

2

Hi @asidawi ,
Welcome to the eazyBI community! :wave:
You can use function DateBeforePeriodEnd() and DateAfterPeriodEnd() for this.
Try this formula:

Sum(
  --get set of issues that have the 'Program start Date' and/or 'Program end Date' in the time period currently selected
  Filter(
    Descendants(
      [Issue].CurrentHierarchyMember,[Issue].[Issue]
    ),
    DateBeforePeriodEnd(
      [Measures].[Issue Start date],
      [Time].CurrentHierarchyMember
    )
    AND
    DateAfterPeriodEnd(
      [Measures].[Issue End date],
      [Time].CurrentHierarchyMember.PrevMember
    )
    ),
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember)
)

In the report it looks like this:

eazyBI_live_issues
eazyBI_live_issues1822×1256 213 KB

Best,
Gerda // support@eazyBI.com

1 Like
3

Thank you Gerda! This is what i was looking for

1 Like