Time Intelligence in Analytics: Trends and Seasonality

Nearly every business number is reported over time, and nearly every misleading report gets time wrong: a seasonal dip presented as a crisis, a partial month compared with a full one, a noisy daily figure read as a trend. "Time intelligence", the name Power BI's DAX language gives to its functions for year-to-date totals and same-period-last-year comparisons (DAX: time intelligence functions), is really a set of habits. This guide works through them on two years of real daily data.

The data

Daily rentals from Washington, D.C.'s Capital Bikeshare system for 2011 and 2012, split into casual riders and registered members (UCI: Bike Sharing). It has everything that makes time comparisons tricky: growth, strong seasons, weekday patterns and noisy days.

import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/275/bike+sharing+dataset.zip"
day = pd.read_csv(zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())).open("day.csv"),
                  parse_dates=["dteday"]).set_index("dteday")
rentals = day["cnt"]
print(len(rentals), "days from", rentals.index.min().date(), "to", rentals.index.max().date())

731 days from 2011-01-01 to 2012-12-31

Month over month versus year over year

monthly = rentals.resample("MS").sum()
table = pd.DataFrame({"rentals": monthly,
                      "MoM %": monthly.pct_change() * 100,
                      "YoY %": monthly.pct_change(12) * 100})
print(table.loc["2012-08":].round(1).to_string())

            rentals  MoM %  YoY %
dteday                           
2012-08-01   214503    5.4   56.9
2012-09-01   218573    1.9   71.5
2012-10-01   198841   -9.0   61.0
2012-11-01   152664  -23.2   49.4
2012-12-01   123713  -19.0   41.7

November 2012 fell 23% from October. Presented alone, that looks like a collapse. Compared with November 2011, rentals were up 49%. Both numbers are correct, but only one answers "is the business doing well?". Month-over-month change mixes real change with seasonality, the regular pattern tied to the time of year (FPP3: time series patterns), and every November is colder than October. Year-over-year change compares like with like, removing the annual season. Use month over month for operational monitoring, where the season is known, and year over year for judging performance.

Smoothing daily noise with moving averages

Daily figures jump around with weather, events and chance. A moving average replaces each day with the average of recent days, which removes much of that randomness and leaves the underlying trend (FPP3: moving averages):

ma7 = rentals.rolling(7).mean()
print(f"days changing more than 20% from the day before: raw {(rentals.pct_change().abs() > 0.2).mean():.1%}, "
      f"7-day average {(ma7.pct_change().abs() > 0.2).mean():.1%}")

days changing more than 20% from the day before: raw 32.0%, 7-day average 0.1%

On almost a third of days, raw rentals moved by more than 20%. The 7-day average almost never does. A seven-day window has a second benefit: it always contains exactly one of each weekday, so the weekly pattern cancels out. A 28-day window does the same over four weeks. The cost is lag: a moving average responds to a real change only gradually, so show it alongside the raw values, not instead of them.

Year to date, compared fairly

Year-to-date totals are only comparable if both years cover the same span:

ytd_2012 = rentals["2012-01-01":"2012-06-30"].sum()
ytd_2011 = rentals["2011-01-01":"2011-06-30"].sum()
print(f"YTD to 30 June: 2011 {ytd_2011:,}, 2012 {ytd_2012:,}, change {ytd_2012 / ytd_2011 - 1:.1%}")

YTD to 30 June: 2011 524,652, 2012 937,675, change 78.7%

This is what DAX functions such as TOTALYTD and SAMEPERIODLASTYEAR do in Power BI. They need a proper date table with one row per day, which is also the fix for months with no data; SQL for data analysts shows the calendar pattern in SQL.

Seasonal indexes: the shape of a year

A seasonal index divides each month's daily average by the year's average, so 1.2 means 20% above a typical month:

by_month = rentals.groupby([rentals.index.year, rentals.index.month]).mean().unstack(0)
index = (by_month / by_month.mean()).round(2).T
print(index.to_string())

dteday    1     2     3     4     5     6     7     8     9     10    11    12
dteday                                                                        
2011    0.36  0.51  0.61  0.93  1.29  1.41  1.34  1.30  1.25  1.17  1.00  0.83
2012    0.56  0.64  0.95  1.04  1.13  1.21  1.17  1.24  1.30  1.15  0.91  0.71

Both years share the shape: well below average in winter and above it from May to October. They differ most in the early months: January 2011 sits much further below average than January 2012. The service grew quickly through 2011, so its early months are low partly because of growth, not season. A seasonal index built on a growing series mixes trend into the season. With several years of history, decomposition methods can separate the two; with two years, treat the index as a rough guide and prefer year-over-year comparisons.

Weekday patterns and mix

Seasonality is not only annual; the day of the week creates its own pattern, and a series can have both:

weekday = day.groupby(day.index.dayofweek)[["casual", "registered"]].mean().round(0)
weekday.index = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
print(weekday.T.to_string())

               Mon     Tue     Wed     Thu     Fri     Sat     Sun
casual       674.0   556.0   551.0   591.0   752.0  1465.0  1338.0
registered  3664.0  3954.0  3997.0  4076.0  3938.0  3085.0  2891.0

Casual riders peak at weekends and members on weekdays. A month with five weekends will show more casual riding than one with four, with no change in behaviour. Compare per-day averages, or same weekday against same weekday, when calendar composition differs.

The partial-period trap

Suppose a report runs on 15 December and compares December to date with the whole of November:

december_to_date, november = rentals["2012-12-01":"2012-12-15"], rentals["2012-11"]
print(f"totals: {december_to_date.sum() / november.sum() - 1:.1%} | per day: {december_to_date.mean() / november.mean() - 1:.1%}")

totals: -47.7% | per day: 4.5%

Compared as totals, December looks 48% down. Per day, it is 4.5% up. Partial periods must be compared with the same partial period, such as the first 15 days of the previous month or the same dates last year, or converted to per-day rates. Dashboards that show "this month so far" next to "last month" make this mistake every day until month end.

Fiscal years and week numbers

Time comparisons also depend on whose calendar is used. Many organisations report on a financial year that does not start in January; in India, for example, the financial year runs from April to March, so "year to date" in May covers two months, not five. Weeks are just as slippery: some systems start them on Sunday, others on Monday, and "week 1" of a year is defined differently from one tool to the next. Agree on the calendar once, build it into the date table, and state it on every report that uses weeks or fiscal periods.

A checklist for time comparisons

• Compare like with like: same length, same season, same day composition.
• Prefer year over year for performance; use month over month with the season in mind.
• Smooth noisy daily data with 7-day or 28-day averages, and show the raw values too.
• Label partial periods and never compare them with complete ones.
• Watch moving holidays such as Easter, Diwali or Eid, which fall in different months in different years.
• Use a complete date table so missing days count as zero rather than disappearing.

These habits underpin forecasting in Excel and the retention curves in cohort analysis. For how time comparisons fit into product metrics, see product analytics 101.

DAX time intelligence functions are covered in the Power BI course.

Sources

• Bike Sharing (UCI Machine Learning Repository) — UCI Machine Learning Repository
• Forecasting: Principles and Practice, section 2.3: time series patterns — Hyndman and Athanasopoulos, OTexts
• Forecasting: Principles and Practice, section 3.3: moving averages — Hyndman and Athanasopoulos, OTexts
• Time intelligence functions (DAX reference) — Microsoft Learn