Search code examples
datessasmdx

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


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


Solution

  • 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.