Archive

Posts Tagged ‘Datediff’

MDX – Datediff

December 18, 2011 3 comments

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]
	 )

Categories: MDX Tags: