Sum Tempo Billed Hours based on EPIC or ISSUE Name

Hey,

I’m trying to calculate values with Tempo Billed Hours by multiple criteria.
For example, I want to collect all hours which have a specific Epic Link or Issue Name (where the epic link is not available) and then use these to calculate a quote.
The report should be on a monthly basis for the last 6 or 12 months. Per day is not so important, but it should be listed per Logged by.

It’s working sometimes and mostly only for 3 Months. As I understood, it’s because I filter on all Issues which makes it’s damming slow and lead to frequent timeouts.
I don’t know how to optimize it. It’s my first contact with eazybi :slight_smile:

Can you help me?

Example 1 - OOO Hours

  CACHE(
    Sum(
      Filter(
          Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
          [Issue].CurrentHierarchyMember.Level.Name = 'Issue'
        ),
        CASE WHEN
            [Issue].CurrentHierarchyMember.Level.Name = 'Issue'
        THEN
            CASE
                WHEN IsEmpty([Measures].[Tempo billed hours])
                  THEN 0.0              
                WHEN CoalesceEmpty([Issue].CurrentMember.Name,'') MATCHES '^OUT.*' 
                  THEN IIf(IsEmpty([Measures].[Tempo billed hours]),0.0,[Measures].[Tempo billed hours])
            END
        END
    )
  )

Example 2 - Billable Hours

Cache(
  Sum(
    Filter(
        Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
        [Issue].CurrentHierarchyMember.Level.Name = 'Issue'
      ),
      CASE WHEN
          [Issue].CurrentHierarchyMember.Level.Name = 'Issue'
      THEN
          CASE
              WHEN IsEmpty([Measures].[Tempo billed hours])
                THEN 0.0              
              WHEN CoalesceEmpty([Issue].CurrentMember.Name,'') MATCHES '^OUT.*' 
                THEN 0.0
              WHEN CoalesceEmpty([Issue].CurrentHierarchyMember.get('Epic Link'),'') MATCHES '^INT.*' 
                THEN 0.0
              ELSE
                [Measures].[Tempo billed hours]
          END
      END
  )
)

Example 3 - Non-Billable Hours

CACHE(
  IIf(
    [Measures].[Total Hours (Clean)]*[Measures].[Billable Hours]>  0,
    100/[Measures].[Total Hours (Clean)]*[Measures].[Billable Hours],
    0.0
  )
)

Thanks & bests
Fabian

It seems you are counting Billed hours for all issues apart from issues with project key OUT and epics with project key INT.

I would suggest counting Billed time and removing billed time from specific project issues and specific epic projects.

The dimension Project represents issues. The dimension Epic Link has a project level representing Project of Epic.

Please check if this measure could work:

[Measures].[Tempo billed hours]
-
-- subtract by epic link project
([Measures].[Tempo billed hours],
 [Epic Link].[INT project name])
-
-- subtract by issue project
([Measures].[Tempo billed hours],
[Project].[OUT project name])
+
-- add back issues with both project and epic to avoid double subtraction
([Measures].[Tempo billed hours],
[Epic Link].[INT project namel],
 [Project].[OUT project name])

Please use correct names for projects - INT project and OUT project.

Daina / support@eazybi.com