Total Issues per month with logged time for the first time


#1

Hi
I need to count the total Issues per month in which work time is logged for the first time.

Can someone help me to get this?


#2

Hi Adriana,

Yes, it is possible to write an MDX calculation that goes through issues and filters the ones that are open in the period and have hours logged in the period but do not have hours logged in Previous periods. Here would be a formula doing that

Sum(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
    NOT DateBeforePeriodEnd(
      [Issue].CurrentMember.get('Resolved at'),
      [Time].CurrentHierarchyMember)), 
    CASE WHEN 
      [Measures].[Hours spent] > 0 AND
      IsEmpty(
      Cache(Sum(
        PreviousPeriods([Time].CurrentHierarchyMember),
        [Measures].[Hours spent]
      ))) THEN 1
    END
)

Going through all issues in this way can be slow though. If you find that the report execution is not fast enough when using the above measure (this strongly depends on how many issues you have in the cube), another option would be to create a custom calculated field (https://docs.eazybi.com/eazybijira/data-import/custom-fields/javascript-calculated-custom-fields) that would go through issue changelog and find the date of the first worklog. After importing such date as a measure, you would get “Issues with first worklog date” measure that would work with Time dimension a lot faster than the above MDX calculation.

Lauma / support@eazybi.com