How to calculate day difference using mdx?

Question

Tell me how to calculate the day difference between two date columns in pentaho schema workbench.

Thanks in advance.

Answer 1

Just to add some dynamic functionality to Harsha's answer you can use:

• the CURRENTMEMBER function to get hold of dates used on ROWS
• you can use VBA date functions to get hold of today's date

So if I run this:

WITH 
  MEMBER Measures.DD AS 
    Datediff
    ('d'
     ,Cdate([Date].[Calendar].CurrentMember.Member_Caption)
     ,Cdate(VBAMDX.Now())
    ) 
SELECT 
  {Measures.DD} ON COLUMNS
 ,[Date].[Calendar].[Date] ON ROWS
FROM [Adventure Works];

It results in this: