Total and roll-up calculated incorrectly

Questions & Answers
1

Hi,

I have a report with items that can have both a ‘due’ and a ‘revised due’ date. I have created the following calculated measure to show issues that are overdue their original due date and don’t have a ‘revised due date’ set:

CASE WHEN
  [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN
  -- When Time dimension is not used, use today
  Sum(
    Filter(
    PreviousPeriods(
      [Time].CurrentHierarchy.Levels('Day').CurrentDateMember
    ),
    IsEmpty([Measures].[Issue Revised Due Date])),
    [Measures].[Issues due]
  )
WHEN
  -- When Time dimension is used, return results for past dates and today only
  DateAfterPeriodEnd(
    'Today',
    [Time].CurrentHierarchyMember.PrevMember
  )
THEN 
  Sum(
    Filter(
    PreviousPeriods(
      [Time].CurrentHierarchyMember
    ),
    IsEmpty([Measures].[Issue Revised Due Date])),
    [Measures].[Issues due]
  )
END

This correctly displays issues matching the criteria, but both the Total and the ‘All Issues’ aggregate values are double the number of matching issues, e.g. if each matching issue has ‘1’ in the calculated measure column, and there are two matching issues, the Total and the aggregate figure for All Issues is 4 instead of 2.

Adding ‘– annotations.total=sum’ fixes the total but not the aggregate figure. Can you help me to identify what’s wrong with my calculated measure please?

2

Hi @dqng

Thanks for posting your question!
You are iterating through days rather than counting the issues.

Please try the below measure and see if it returns the expected results

Sum(
    Filter(
      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      -- Issue has a due date
      NOT IsEmpty([Measures].[Issue due date])
      AND
      -- Due date is in the past (overdue)
      DateCompare([Measures].[Issue due date], 'Today') < 0
      AND
      -- No Issue Revised Due Date  is set
      IsEmpty([Measures].[Issue Revised Due Date])
      AND
      -- Issue is not resolved (optional - remove if you want all overdue)
      IsEmpty([Measures].[Issue resolution date])
    ),
    [Measures].[Issues due]
  )

Best wishes,

Elita from support@eazybi.com