I have a dimension called DimAgent, this is the Employee Dimension. Within this dimension I have the EmployeeID, Name, HireDate ect.
I would like to calculate the Tenure of the Employee. Would this be best achieved using a calculated member? If calculated members are calculated when the cube is complied, I don't think that will work. The Tenure should be calculated when the
report is being rendered, therefore I would think this needs to be a Calculation in the cube.
I am not sure of the syntax, as this field is really a calculation on the dimension and not a fact.
I tried this:
With Member [Measures].[Tenure] as
'DateDiff("M", [Dim Agent].[Hire Date], Now())'
Select ([Measures].[Tenure]) on 0,
[Dim Agent].[Agent Name].[Agent Name].Members on 1
But it results in the same number of months for all Agents. This must be the total months of all the agents, but I would like the number of months for each agent separately.
Any help is appreciated.
View Complete Post