gosi75
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]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did use datediff, but I added it to my cube as a measure.
ASKER