Calculated Measure using Datediff

Hello out there,

i am pretty new to SSAS and have to add a calculated measure that uses the Datediff function from T-SQL.

In detail I want to calculate the average Datediff between a selected Date and the Dates in the DB Table DimUsers.

For example:

UserID = 1 - FirstVisitDate = 05-01-2005
UserID = 2 - FirstVisitDate = 07-05-2005
UserID = 3 - FirstVisitDate = 07-19-2005
.
.
.

And now I want to display the average DateDiff in Days between those Datetimes and a selected Date (let's take 01-07-2006) for showing it in the Cube Browser.

I hope I explained it clear enough and someone please show me a detailled way to do that.
Thanks a lot in advance!
Greets
Claudio

Here's an Adventure Works query modified from an earlier thread - it computes the average difference of the Start Date of a Promotion from a given date (here it's 2005-01-01):

>>

With Member [Measures].[AvgDaysSinceStart] as

Avg(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Start Date].MemberValue,

CDate("2005-01-01"))),

FORMAT_STRING = '#,#.00'

select {[Measures].[AvgDaysSinceStart]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

--

AvgDaysSinceStart
All Promotions 876.13
Discontinued Product 603.50
Excess Inventory 677.00
New Product 550.00
No Discount 1,310.00
Seasonal Discount 656.67
Volume Discount 1,280.00

>>

|||Hello Deepak,

wow thank you for your quick answer again!

It's almost what i need. How can I use a dynmaic value instead of CDate("2005-01-01")?

I use the Server Time Dimension and tried

AVG(existing [Dim User].[Dim User].[Dim User], DateDiff("d", [Dim User].[FIRST VISIT DATE].MemberValue, [Time].[Year - Half Year - Quarter - Month - Date].[Date].MemberValue))

that is not working.
|||

Well, it depends on how the dynamic value is defined. For example, if it should be the last date in the [Date] dimension:

With Member [Measures].[AvgDaysSinceStart] as

Avg(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Start Date].MemberValue,

Tail([Date].[Date].[Date]).Item(0).MemberValue)),

FORMAT_STRING = '#,#.00'

select {[Measures].[AvgDaysSinceStart]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

|||Thats closer to what i am looking for. But how can I just use it full dynamic? I want to go to the Cube Browser and select a Date in the <select dimension> on top together with the calculated measure in the data area.

I guess (but i'm not sure) it's a similar approach as it is used in the Calculate measure "Growth in Customer Base" from Adventure Works

Case

When [Date].[Fiscal].CurrentMember.Level.Ordinal = 0
Then "NA"

When IsEmpty
(
(
[Date].[Fiscal].CurrentMember.PrevMember,
[Measures].[Customer Count]
)
)
Then Null

Else (
( [Date].[Fiscal].CurrentMember, [Measures].[Customer Count] )
-
( [Date].[Fiscal].PrevMember, [Measures].[Customer Count] )
)
/
( [Date].[Fiscal].PrevMember,[Measures].[Customer Count] )

End

... is it?|||

With Member [Measures].[AvgDaysSinceStart] as

Avg(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Start Date].MemberValue,

[Date].[Date].MemberValue)),

FORMAT_STRING = '#,#.00'

select {[Measures].[AvgDaysSinceStart]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

where [Date].[Date].[July 1, 2004]

|||Thanks a lot Deepak! That works perfect!
What should I do without all your help? You are great!

Greets
Claudio