Link to home
Start Free TrialLog in
Avatar of gosi75
gosi75Flag for Iceland

asked on

Counting total days between two dates

I have two dates in my cube, date1 and date2.  I need to count days between those two dates and then sum the result by year basis. I have created a similiar scenario on the Adwenture works cube where I'm trying to show total of days between ship date and delivery date and then show the result by each year by category. I get #error in the 2005 and 2006 measure, how can I show the correct result?

WITH 


MEMBER [Measures].[Ship Date] AS [Ship Date].[Date].membervalue
MEMBER [Measures].[Delivery Date] AS [Delivery Date].[Date].membervalue
MEMBER [Measures].[# of Delivery Days] AS [Measures].[Delivery Date] - [Measures].[Ship Date]
MEMBER  [Measures].[2005] as SUM([Measures].[# of Delivery Days],[Ship Date].[Calendar].[Calendar Year].&[2005])
MEMBER  [Measures].[2006] as SUM([Measures].[# of Delivery Days],[Ship Date].[Calendar].[Calendar Year].&[2006])

SELECT	{
	
[Product].[Category].[Category]
	} ON COLUMNS,
	
	( {[Measures].[2005],[Measures].[2006]}

	) ON ROWS
FROM	[Adventure Works]

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of plusone3055
plusone3055
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gosi75

ASKER

I'm using OLAP so I don't think DATEDIFF will work for me. Any other succession :)
Avatar of gosi75

ASKER

I did use datediff, but I added it to my cube as a measure.