Calculated Members with dates from FactTable

I just started with MDX in SSAS and I have to admit I'm a little confused ;-)

So, what I would like to calculate is rather simple. In a fact table FactDelivery we have two date key fields, CreationDateKey and DeliveryDateKey.

The processing time is the number of days between the creation of the entry and the actual delivery date.

When creating a new calculated member, we have in the lower left window the fields of the measures and the dimensions. But since the date values of the FactDelivery are not actual measures, the are of corse not listed. I would have expected that I can access them in the following fashion.

Case 
  When [FactDelivery].[DeliveryDateKey] < [Fact Delivery].[DateCreatedKey] 
  Then 0 
  Else [FactDelivery].[DeliveryDateKey] - [FactDelivery].[DateCreatedKey]
End

This looks just like a named calculation that you can define on the fact table itself. This was my first approach. But since I only have the keys stored as integers YYYYMMDD, I can't realy do date calculations. That's why I switched to MDX, to have access to the actual dates in the DimDate table. Any help is appreciated.

Best regards

Sebastian

Solution

You are absolutely on the right track with "named calculation"...the logic is just not as simple as you'd like ;-)

Below is an example of converting an integer date (YYYYMMDD) into a date and then using DateDiff function to calculate the days between.

DECLARE @s_dt_int AS INT = 20141205;
DECLARE @e_dt_int AS INT = 20141208;

SELECT  DATEDIFF(
            DAY,
            CONVERT(date, CONVERT(varchar(8), @s_dt_int), 112),
            CONVERT(date, CONVERT(varchar(8), @e_dt_int), 112)
        )
;

This logic can be encapsulated in a view on the source or in the DSV of the SSAS database (though I prefer the view on the source and consider it good practice since it can be reused across multiple ssas databases)

Edit: obviously you can wrap the DateDiff in the CASE statement to control handling of situations where CreateDateKey > DeliveryDateKey.