MDX function calculation

Questions & Answers
,
1

Need help building a report.
I’m trying to get two values.
1)all issue that are completed before the expiration due date (due date => resolved day)

2)all issue that are not completed before the due date or completed after the due date ( due date < resolved day or (resolved day is empty AND due date < The current date))

2

Hi,

In this case, you could try defining new calculated members in “Measures” dimension

  1. Issues resolved before due date

    NonZero(
    Count(
    Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    Not IsEmpty([Issue].CurrentHierarchyMember.get(‘Resolved at’))
    AND
    Not IsEmpty([Issue].CurrentHierarchyMember.get(‘Due date’))
    AND
    DateCompare(
    DateWithoutTime([Issue].CurrentHierarchyMember.get(‘Resolved at’)),
    [Issue].CurrentHierarchyMember.get(‘Due date’)
    )<=0
    AND
    [Measures].[Issues resolved]>0
    )
    )
    )

  2. Issues that are not completed before due date or completed after due date

    NonZero(
    Count(
    Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    Not IsEmpty([Issue].CurrentHierarchyMember.get(‘Due date’))
    AND
    (
    (
    IsEmpty([Issue].CurrentHierarchyMember.get(‘Resolved at’))
    AND
    DateCompare(
    [Issue].CurrentHierarchyMember.get(‘Due date’),
    ‘today’
    )<0
    )
    OR
    DateCompare(
    DateWithoutTime([Issue].CurrentHierarchyMember.get(‘Resolved at’)),
    [Issue].CurrentHierarchyMember.get(‘Due date’)
    )>0
    )
    AND
    [Measures].[Issues with due date]>0
    )
    )
    )

Martins / eazyBI Support