SQL Server SSAS Calculated Measure with DateDiff

I'm trying to create a calculated member in SSAS using the datediff function, but can't seem to get it working at all.

The requirement is as follows, datediff(day, XDate where Type = 2 AND Status IN (1,2,3), XDate where Type = 3)

I'm not sure on how to translate that to the proper MDX for a calculated measure in SSAS.

Any help is greatly appreciated.

Answers


Here is a sample query showing a calculated measure called DateDiff. I prefer to break down complex calculations into smaller parts for easier troubleshooting. You can combine if you like. I've invented some dimensions and measures here but hopefully you can see how this works and adapt accordingly. Basically, I'm getting a set that combines a record with specific status codes (or type codes) and getting all the related dates, but only those that have a fact table row for a measure called Status Count (arbitrary - any measure in fact table will do). Then I pick off the last one - assuming all is in date order which it should be if your dimension sort is correct, and pick out the date with extract - and then because it's a set - reduce it to a member with Item(0).Item(0). Then get its name for use in the DateDiff function. I've included some logic to make sure the current record is not the All member and that the first date is not empty.

    with 
    member [Measures].[First Date] as
      iif(Record.Record.CurrentMember Is [Record].[Record].[All],
      null,
Extract(
Tail(
NonEmpty(
    (Record.Record.CurrentMember, 
    {[Status Code].[Status].&[1],[Status Code].[Status].&[2],[Status Code].[Status].&[2]},
    [Date].[Date].[Date].Members),
    [Measures].[Status Count]), 1), [Date].[Date])
).Item(0).Item(0).Name

member measures.[Second Date] as

iif(Record.Record.CurrentMember Is [Record].[Record].[All],
null,

Extract(
Tail(
NonEmpty(
    (Record.Record.CurrentMember, 
    [Type].[Type].&[3],
    [Date].[Date].[Date].Members),
    [Measures].[Status Count]), 1), [Date].[Date])
).Item(0).Item(0).Name

member measures.[DateDiff] as 

iif(IsEmpty([Measures].[First Date]), null, 
datediff("d", [Measures].[First Date], [Measures].[Second Date]))

select
{[Measures].[Status Count], 
[Measures].[First Date], 
[Measures].[Second Date], 
[Measures].[DateDiff]}
on columns,
[Record].members on rows
from [status]

Need Your Help

adding resources to static library in iPhone

iphone xcode image plist static-libraries

I am going to create a Static Library, But I am facing problem while adding my images and plists to it.

Symfony 2 removing form fields created using form type

symfony doctrine

I have an EmployeeBasicInfoEntity having employee fields with some validation constraints using annotation. I am using this entity for employee Registration. Here is my EmployeeBasicInfoType