Moving averages smooth the price data to form a trend following indicator. They do not predict price direction, but rather define the current direction with a lag. Moving averages lag because they are based on past prices. Despite this lag, moving averages help smooth price action and filter out the noise. They also form the building blocks for many other technical indicators and overlays, such as Bollinger Bands, MACD and the McClellan Oscillator. The three most popular types of moving averages are the Simple Moving Average (SMA) and Weighted Moving Average(WMA) the Exponential Moving Average (EMA). These moving averages can be used to identify the direction of the trend or define potential support and resistance levels.
Simple Moving Average(SMA)
A simple moving average is formed by computing the average price of a security over a specific number of periods. Most moving averages are based on closing prices. A 5-day simple moving average is the five day sum of closing prices divided by five. As its name implies, a moving average is an average that moves. Old data is dropped as new data comes available. This causes the average to move along the time scale. Below is an example of a 5-day moving average evolving over three days.
Daily Closing Prices: 11,12,13,14,15,16,17 First day of 5-day SMA: (11 + 12 + 13 + 14 + 15) / 5 = 13 Second day of 5-day SMA: (12 + 13 + 14 + 15 + 16) / 5 = 14 Third day of 5-day SMA: (13 + 14 + 15 + 16 + 17) / 5 = 15
The first day of the moving average simply covers the last five days. The second day of the moving average drops the first data point (11) and adds the new data point (16). The third day of the moving average continues by dropping the first data point (12) and adding the new data point (17). In the example above, prices gradually increase from 11 to 17 over a total of seven days. Notice that the moving average also rises from 13 to 15 over a three day calculation period. Also notice that each moving average value is just below the last price. For example, the moving average for day one equals 13 and the last price is 15. Prices the prior four days were lower and this causes the moving average to lag.
In a Moving aggregation , the important technique is to construct a range with in the level using endpoints that are relative to the current member
we can create this range using many functions in MDX depending upon the range
Examples
Averages for 6 months range
Avg({[date].[month].currentmember.Lag(5): [date].[month]},[Measures].[sales])
Averages for 6 month range
Avg(Lastperiods(6,[time].[month].currentmember),[Measure].[sales])
Average of current period and prior period
Avg({[date].[month].currentmember.prevmember, [date].[month].currentmember},[Measure].[sale])
Using Parallel periods
Avg({parallelperiods([date].[halfyear],1,[date].[currentmember]):[date].currentmember]},[Measure].[sale])
Here is the example on Adventure Works cube
WITH
MEMBER [Measures].[SMA12] AS Avg(
[Ship Date].[Month Name].CurrentMember.Lag(11):[Ship Date].[Month Name],
[Measures].[Internet Sales Amount]
)
MEMBER [Measures].[SMA6] AS Avg(
[Ship Date].[Month Name].CurrentMember.Lag(5):[Ship Date].[Month Name],
[Measures].[Internet Sales Amount]
)
MEMBER [Measures].[SMA3] AS Avg(
[Ship Date].[Month Name].CurrentMember.Lag(2):[Ship Date].[Month Name],
[Measures].[Internet Sales Amount]
)
SELECT
{[Measures].[Internet Sales Amount], [Measures].[SMA12], [Measures].[SMA6], [Measures].[SMA3]} ON 0
,[Ship Date].[Calendar].[Month] ON 1
FROM [Adventure Works]
code
with
member
[Measures].[avg12ms] as
avg([Date].[Month of Year].lag(11):[Date].[Month of Year] ,[Measures].[Internet Sales Amount])
member [Measures].[avg6ms] as
avg([Date].[Month of Year].lag(5):[Date].[Month of Year] ,[Measures].[Internet Sales Amount])
member [Measures].[avg3ms] as
avg([Date].[Month of Year].lag(2):[Date].[Month of Year] ,[Measures].[Internet Sales Amount])
select {[Measures].[Internet Sales Amount],[Measures].[avg12ms],[Measures].[avg6ms],[Measures].[avg3ms]} on columns
,{[Date].[Calendar].[Month]} on rows
from [Adventure Works]
Syntax
Sqljunkieshare 