I have a shell date dimension, and a sale date dimension. Having trouble setting up a calculated measure with the difference in days between the 2 dates. I have tried a number of things, and the calculation always seems to return an error.
mdx
example is:
WITH
MEMBER [Measures].[TimeDate] AS [Date].[Day].currentmember
MEMBER [Measures].[DSODate] AS [DSO Date].[Day].currentmember
MEMBER [Measures].[DaysSinceSale] AS
DateDiff(
"d"
, [Measures].DSODate.MemberValue
, [Measures].TimeDate.MemberValue
)
Select
{[Measures].[DaysSinceSale]} ON COLUMNS,
{[Date].[Day].members} ON ROWS
from [Receivables];
I have tried using DateDiff, and tried just subtracting the 2 dates. Assuming it may have something to do with the 2 date dimensions being of different hierarchies, but i am not really sure how to handle that.
I found a way to get this to work ... Main issue was that i didn't have a crossjoin, like whytheq mentioned. I also didn't need the custom Measures declared at the top.
The other adjustment i made was to utilize the DateKey in the date calculation. That seemed to work in all my tests, and improved performance quite a bit.
WITH
MEMBER [Measures].[DaysSinceSale] AS
[Date].[DateKey].CurrentMember.MemberValue - [DSO Date].[DateKey].CurrentMember.MemberValue
Select
{[Measures].[DaysSinceSale]} ON COLUMNS,
{[Date].[DateKey].Members * [DSO Date].[DateKey].members} ON ROWS
from [Receivables];
If you see any issues that may arise with using DateKey let me know. For what i am doing that seemed to pull back the correct date value, and allowed me to find the difference between dates without using a datediff function.