MDX Calculating Time Between Events

I have a Cube which draws its data from 4 fact/dim tables.

1. FactCaseEvents (EventID,CaseID,TimeID)
2. DimEvents (EventID, EventName)
3. DimCases (CaseID,StateID,ClientID)
4. DimTime (TimeID,FullDate)

Events would be: CaseReceived,CaseOpened,CaseClientContacted,CaseClosed

DimTime holds an entry for every hour.

I would like to write an MDX statement that will get me 2 columns: "CaseRecievedToCaseOpenedOver5" and "CaseClientContactedToCaseClosedOver5"

CaseRecievedToCaseOpenedOver5 would hold the number of cases that had a time difference over 5 hours for the time between CaseReceived and CaseOpened.

I'm guessing that "CaseRecievedToCaseOpenedOver5" and "CaseClientContactedToCaseClosedOver5" would be calculated members, but I need some help figuring out how to create them.

Thanks in advance.

From stackoverflow

• This looks like a good place to use an accumulating snapshot type fact table and calculate the time it takes to move from one stage of the pipeline to the next in the ETL process.

  madcolor : thanks for the comment..I'll look into that.

• Query for AdventureWorks (DateDiff works in MDX):

  WITH 
  MEMBER Measures.NumDays AS 
  'iif(ISEMPTY(([Delivery Date].[Date].CurrentMember
  ,[Ship Date].[Date].CurrentMember
  ,Measures.[Order Count]))
  ,null
  , Datediff("d",[Ship Date].[Date].CurrentMember.Name
  ,[Delivery Date].[Date].CurrentMember.Name))'
  SELECT
  NON EMPTY {[Ship Date].[Date].&[63]
  :[Ship Date].[Date].&[92]} ON COLUMNS,
  NON EMPTY {[Delivery Date].[Date].&[63]
  :[Delivery Date].[Date].&[92]} 
  * {[Measures].[NumDays]
  , [Measures].[Order Count]} ON ROWS
  FROM [Adventure Works]
  

  Taken from: http://www.mombu.com/microsoft/sql-server-olap/t-can-i-have-datediff-in-mdx-265763.html

  If you'll be using this member a lot, create it as a calculated member in the cube, on the Calculations tab if I remember right.

  madcolor : Killer.. thanks.