MDX to create a rolling 30 average sales

Question

MDX to create a rolling 30 average sales

jack-64275

SSCrazy

Points: 2017
More actions
October 15, 2008 at 12:38 pm

#192237

Is it possible in AS to create a calculated member that is 30 days rolling average sales. So average daily sales over a 30 day rolling period.
Thanks in advance!
Jim

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

dva2007 SSCertifiable Points: 7707 More actions · Answer 1

May i ask you to give some more detail? It is bit confusing what you are looking for. If you can give some simple example.

jack-64275 SSCrazy Points: 2017 More actions · Answer 2

Yes. I would like to be able to select a date and have a calculation that will return the average daily sales based on 30 days from the date select. So if I selected today as my starting date I want to take the daily sales for the past 30 days and get the average of those daily sales.

I hope this makes it clearer.

dva2007 SSCertifiable Points: 7707 More actions · Answer 3

Hi,

i hope you know about how to take average. Now if you have parameter in report or in stored procedure then you can pass that parameter into your query like below:

i.e. ur parameter is say - @startdate and your date of all sales will be say - salesdate

u need to write query where you can take your average

select avg(sales) as avg_sales

from yourtable

where saslesdate is between dateadd(mm, -1, @startdate) and @startdate

This query will return you average sales of last 30 days from @startdate.

Regards,

Vijay

jack-64275 SSCrazy Points: 2017 More actions · Answer 4

The problem is that I'm not trying to do this using sql. The goal is to add this as a calculated member in my SSAS Cube for business users to use for analysis. That is why I am looking for MDX to perform this.

Here is what I have but it's not working as expect:

Avg( LASTPERIODS(30,[Order Date].[Actual Date].CurrentMember), [Measures].[Order Line Regular plus Web Sales Total] )

Thanks.

upperbognor Right there with Babe Points: 787 More actions · Answer 5

Have you tried something like this? I found it in the built in templates under calculations for the cube. The function template is called moving average under the time series.

Avg

(

[Target Dimension].[Target Hierarchy].CurrentMember.Lag(Periods to Lag) :

[Target Dimension].[Target Hierarchy].CurrentMember,

[Measures].[Target Measure]

)

// This calculation returns the average value of a member over the specified time interval.

Pete Brown SSC Eights! Points: 984 More actions · Answer 6

using your example

Avg( LASTPERIODS(30,[Order Date].[Actual Date].CurrentMember), [Measures].[Order Line Regular plus Web Sales Total] )

try

iif([Measures].[Order Line Regular plus Web Sales Total]<>0,Avg( LASTPERIODS(30), [Measures].[Order Line Regular plus Web Sales Total] ),null)

this will give you the average over the last 30 'time periods' depending what the user has selected (e.g. years, quarters,months,weeks or days)

This will give your users maximum flexibility.

We use measures like this all the time for trend graphs etc.