MDX number of days between shell date dimension and regular date dimension

1
votes

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.

MDX Results

2
datessasmdx

2 Answers

0
votes

Date conversions can be tricky in mdx so maybe initially try the following simple approach:

WITH 
MEMBER [Measures].[TimeDate] AS [Date].[Day].currentmember
MEMBER [Measures].[DSODate]  AS [DSO Date].[Day].currentmember
MEMBER [Measures].[DaysSinceSale] AS 
   DateDiff(
      "d"
      , VBA!CDate([Measures].DSODate.MemberValue)
      , VBA!CDate([Measures].TimeDate.MemberValue)
   )
Select 
  {[Measures].[DaysSinceSale]} ON COLUMNS,
  {[Date].[Day].members} ON ROWS
from [Receivables];

Otherwise you might need to use the key and an approach similar to this:
MDX - Converting Member Value to CDate

0
votes

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.