Moving Average Formula: Types, Calculations, and Practical Uses

A moving average smooths out short-term fluctuations to reveal underlying trends. Instead of looking at individual data points (which can be noisy), a moving average shows the average of the last N periods, recalculated at each step. It is one of the most widely used techniques in business analytics, finance, and operations.

Simple Moving Average (SMA)

The basic form: average the last N values.

Formula: SMA = (V1 + V2 + ... + VN) / N

Example (7-day SMA of daily revenue):

The SMA only starts producing values once you have N data points.

SQL implementation:

SELECT
    order_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS sma_7day
FROM (
    SELECT order_date, SUM(amount) AS daily_revenue
    FROM orders
    GROUP BY 1
) daily
ORDER BY order_date;

Weighted Moving Average (WMA)

Assigns more weight to recent observations and less weight to older ones.

Formula: WMA = (W1 x V1 + W2 x V2 + ... + WN x VN) / (W1 + W2 + ... + WN)

Example (3-period WMA with weights 3, 2, 1):

• Day 1 revenue: $10,000 (weight 1, oldest)
• Day 2 revenue: $12,000 (weight 2)
• Day 3 revenue: $15,000 (weight 3, most recent)
• WMA = (1x10,000 + 2x12,000 + 3x15,000) / (1+2+3) = $79,000 / 6 = $13,167

Compared to SMA: (10,000 + 12,000 + 15,000) / 3 = $12,333

The WMA responds faster to recent changes because it gives them more influence.

Exponential Moving Average (EMA)

Gives exponentially decreasing weights to older observations. Unlike SMA and WMA, EMA considers all past values (not just the last N), with influence decaying exponentially.

Formula: EMA_today = (Value_today x k) + (EMA_yesterday x (1 - k))

Where k (smoothing factor) = 2 / (N + 1)

Example (10-day EMA, k = 2/11 = 0.182):

• Yesterday's EMA: $14,000
• Today's value: $16,000
• Today's EMA = ($16,000 x 0.182) + ($14,000 x 0.818) = $2,909 + $11,455 = $14,364

Properties:

• Responds faster to recent changes than SMA
• Never fully "forgets" old data (just gives it exponentially less weight)
• Widely used in financial analysis (MACD indicator uses 12-day and 26-day EMAs)

Comparison of Moving Average Types

Choosing the Period (N)

The window size controls the tradeoff between smoothness and responsiveness:

Business Applications

Revenue Trend Detection

-- 30-day moving average to smooth daily revenue volatility
SELECT
    order_date,
    daily_revenue,
    AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30
FROM daily_revenue_table
ORDER BY order_date;

When the daily value crosses above or below the moving average, it signals a potential trend change.

Demand Forecasting

Manufacturing and retail use moving averages as simple demand forecasts:

• Forecast for next period = Moving average of last N periods
• Adjust N based on product lifecycle (shorter for trending items, longer for stable goods)

Anomaly Detection

Define anomalies as values that deviate significantly from the moving average:

WITH stats AS (
    SELECT
        order_date,
        daily_revenue,
        AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30,
        STDDEV(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS std_30
    FROM daily_revenue
)
SELECT *
FROM stats
WHERE ABS(daily_revenue - ma_30) > 2 * std_30;  -- More than 2 standard deviations from MA

KPI Dashboards

Display both raw metrics and moving averages so users can distinguish noise from signal:

• Raw daily metric: volatile, hard to interpret
• 7-day MA: shows weekly trend
• 30-day MA: shows monthly trend

Finance (Technical Analysis)

• SMA crossovers: When short-term MA crosses above long-term MA (bullish signal)
• Bollinger Bands: SMA plus/minus 2 standard deviations (volatility indicator)
• MACD: Difference between 12-day and 26-day EMA (momentum indicator)

Implementation Tips

1. Handle the warm-up period. The first N-1 values have incomplete windows. Either display NULL or use expanding windows for those periods.
2. Choose cumulative vs. trailing. A trailing MA uses the last N values. A cumulative MA uses all values up to the current point (gets smoother over time).
3. Consider business cycles. Use 7-day windows for metrics with weekly seasonality (website traffic, orders). Use 30-day for monthly cycles.
4. Watch for composition changes. If your customer base changed dramatically (new segment, market expansion), historical MAs may not be comparable to current ones.

Summary

Moving averages are the simplest and most effective technique for separating signal from noise in time-series data. Choose your window size based on the tradeoff between responsiveness (shorter) and stability (longer).