Home > MDX > MDX – Datediff

MDX – Datediff

Recently, we got a scenario of finding the date difference while developing a dashboard. I am much interested to document the process and it will be useful in future as it is a common scenario in dashboard requirements apart from any business industry.
Datediff – Calculate the difference of days between [Due date] and [Ship date] as

WITH MEMBER [Measures].[Order Processing Days] AS 
IIF(ISEMPTY(([Delivery Date].[Date].CurrentMember
 ,[Ship Date].[Date].CurrentMember
 ,Measures.[Internet Sales Amount]))
 ,null
 ,Datediff("d",[Ship Date].[Date].CurrentMember.Name
 ,[Delivery Date].[Date].CurrentMember.Name))

SELECT {[Measures].[Order Processing Days],Measures.[Internet Sales Amount]} ON COLUMNS
, NON EMPTY (
			 ([Ship Date].[Date].[Date].MEMBERS - [Ship Date].[Date].[Date].[All])
			*([Delivery Date].[Date].MEMBERS - [Delivery Date].[Date].[All]) 
			* [Date].[Calendar].[Month].members
			)
			ON ROWS
FROM (
		SELECT [Date].[Calendar].[Calendar Year].&[2007] ON COLUMNS
		FROM 
		[Adventure Works]
	 )


 
Updated the MDX to have the year dimension as it is a common scenarios to have date differences to year

WITH MEMBER [Measures].[Order Processing Days] AS 
IIF(ISEMPTY(([Delivery Date].[Date].CurrentMember
 ,[Ship Date].[Date].CurrentMember
 ,Measures.[Internet Sales Amount]))
 ,null
 ,Datediff("d",[Ship Date].[Date].CurrentMember.Name
 ,[Delivery Date].[Date].CurrentMember.Name))

SELECT {[Measures].[Order Processing Days],Measures.[Internet Sales Amount]} ON COLUMNS
, NON EMPTY (
			 ([Ship Date].[Date].[Date].MEMBERS - [Ship Date].[Date].[Date].[All])
			*([Delivery Date].[Date].MEMBERS - [Delivery Date].[Date].[All]) 
			* [Date].[Calendar].[Calendar Year].members
			)
			ON ROWS
FROM (
		SELECT [Date].[Calendar].[Calendar Year].&[2007] ON COLUMNS
		FROM 
		[Adventure Works]
	 )

Advertisements
Categories: MDX Tags:
  1. March 25, 2015 at 9:32 pm

    I am getting wrong result while calculating date difference between two dates.

    /*** Query ***/

    With Member Measures.DD as DateDiff(‘d’,CDate(‘Mar 20, 2014’),CDate(‘Apr 19, 2014’))
    select {Measures.DD} on Columns
    from [Cube_AR]

    /*** Result ***/
    1

    /*** Expected Result ***/
    30

  2. March 27, 2015 at 6:29 am

    Berkin,
    Try this
    With Member Measures.DD as DateDiff(“d”,CDate(‘March 20, 2014’),CDate(‘April 19, 2014’))
    select {Measures.DD} on Columns
    from [My Cube]

    It worked for me and I think, it is due to short month names.

  3. April 6, 2015 at 5:28 pm

    It is working well in SSAS MDX but my problem is in Mondrian MDX

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: