Using DateDiff in SSAS MDX

Question

I would like to write a KPI in SSAS which gives me back the average of all the employee's age. The employee's Birth Date is in Dim_Employee. I read 3 books full of MDX date and time handling recommendations already, but neither one worked. With hours of desperate trial and error I tried countless combination to the solution without success.

The Birth Date is datetime(null) in the source database. The solution I tried is the following:

VBA!DateDiff("yyyy", now(), [Employee].[BirthDate].CurrentMember.Member_Value)

Of course I should use [Date].[Date].CurrentMember instead of now(), but for simplicity I used this.

In the Employee dimension, I created a ValueColumn with Date datatype. When I try to execute it in Management Studio, it gives me back the following error:

"The Axis0 function expects a tuple set expression for the argument. A string or numeric expression was used."

When I do not use Member_Value, it gives back null, and DateDiff gives back -2010.

Because I'm not responsible for the cube's structure where I would like to write this KPI I search for a solution which does not require new Measures, Dimensions at all. (however if there is no solution without adding new elements to the cube then I will of course propose a change request in the given cube)

What is the solution in this case? Is it possible to write this KPI without using additional Measures?

Answer 1

Answering my own question.

It looks like this cannot be solved as I tried. Finally I added a new column under T-SQL to the Fact_Headcount which now uses INNER JOINs to both Dim_Employee and Dim_Date and I use T-SQL's DateDiff to calculate ages for every employee with every given datetime. Now I added Age as a Measure to this HeadCount MeasureGroup and now I can manage to do this KPI calculation.

Which means that I have to make modifications to the underlying model to solve the case.

Answer 2

Try using the CDATE function:

VBA!DateDiff("yyyy", now(), CDate([Employee].[BirthDate].CurrentMember.Member_Value))

Answer 3

I accept with your answer, We could also do this like

Datediff("yyyy",Now(),[Delivery Date].[Date].CurrentMember.Name)

-- Here the format of the Now() and the member is making issue. When I did at [Adventure Works] Cube with correct foramt acctually I am getting datediff in years