Avareges for period

Hi,

I am trying to get average for sales for last 30 days.

Aggregation function for [Measures].[Avg Sales] is AverageOfChildren

Next code returns correct result

SELECT { [Measures].[Avg Sales] } ON columns

FROM [Sales]

where ([Dim Date].[Year - Day].[Day].&[2/14/2007]&[2]:[Dim Date].[Year - Day].[Day].&[3/15/2007]&[3])

But this query is not flexible. Should be selected day and returned value for 30 days back.

For example, I am trying to use next code

select

([Dim Date].[Year - Day].[Day].CurrentMember.lead(29) : [Dim Date].[Year - Day].[Day].CurrentMember

, [Measures].[Avg Sales]) // average for 30 days back

ON columns

FROM [SAles]

where ([Dim Date].[Year - Day].[Day].&[3/15/2007]&[3])

But recieved error "The Tuple function expects a tuple expression for the argument. A tuple set expression was used."

What the query should be to resolve this problem?

Thanks for help.

Try creating a query calculated measure for this,like:

With

Member [Measures].[Trailing30Sales] as

Aggregate({[Dim Date].[Year - Day].Lag(29) : [Dim Date].[Year - Day].CurrentMember},

[Measures].[Avg Sales])

select

{[Measures].[Trailing30Sales]} // average for 30 days back

ON columns

FROM [Sales]

where ([Dim Date].[Year - Day].[Day].&[3/15/2007]&[3])

|||

Thank you, very much!

It is very simple