MDX Calculated member to compare dates for SLA purposes

[eo]

Hi,

I have a task table, which contains Task Start Date, Task Due Date and Task Completed Date. I want to use this information to calculate a variety of things.

I created a degenerate dimension from the FACT_Task table called TimeTransactionTask with the three datetime fields as dimension members. How can I now use this to calculate (for example) if the task falls within its service level agreement (I.e. where Task Completed Date <= Task Due Date).

On the surface this seems simple, but the calculated member below does not provide the correct results. I am assuming it is because the evaluation is not hppening at the most granular level. Any ideas?

CREATE MEMBER CURRENTCUBE.[MEASURES].[TaskUnderSLA]
    AS SUM(IIF([TimeTransactionTask].[TaskComplDateDATETIME].CurrentMember
            <=[TimeTransactionTask].[TaskDueDateDATETIME].CurrentMember
            ,1,0)),
   FORMAT_STRING = "#,#",
   VISIBLE = 1;

EO
Hertfordshire, England

 

[RiverGuy]

The best solution would be to add a column to your fact table to denote the 1 or 0, then a measure to sum those.