Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Simple but good enought trend analysis

Posted by thomasivarssonmalmo on May 25, 2007

Trend analysis with MDX is an interesting topic. Both Chris Webb and Mosha have blogged about this subject before.
Here is Chris blogpost:
Using Linear Regression to Calculate Growth(
And here is Moshas:
And in "Fast Tracks to MDX", second edition(Springer) there is an example of using moving average as a trend method(chapter 8)
The suggestion I have to this problem came from the feedback from business controllers in a manufacturing company.
I have nothing advanced to tell about this subject only to write some lines of what this customer prefered. The trend calculation they wanted was an accumulated rolling 12 months measure, due to seasonal variations in sales.
Here is an example of a calculated member for this.
(SUM(YTD([Time].[Time_Calendar]),[Measures].[Sales USD])+
Sum(Ancestor(ParallelPeriod([Year],1,[Time].[Time_Calendar].Currentmember),[Year]).Children,[Measures].[Sales USD]))
– Sum(YTD(ParallelPeriod([Year])),[Measures].[Sales USD])
The solution is a little bit ugly but I take this years YTD add last Year total sales and subtract last year YTD.
There are many more solutions to this problem but accumulated sales as a trend measure was easier to understand for my customer.
I would like to hear more about some better solutions.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: