Calendar Completion and Missing Dates

Why ti fills your calendar, and what it refuses to fill

Author

Alejandro Hagan

The problem with gaps

Transactional data has holes in it. Stores close on holidays, no one orders on a Sunday, a product sells nothing for a week. Your table has no row for those days — not a row with a zero, no row at all.

That is fine until you compare periods. Consider a week of margin data with three days missing:

gappy <- tibble::tibble(
  order_date = as.Date(c("2024-01-01", "2024-01-03", "2024-01-06",
                         "2024-01-07", "2024-01-08", "2024-01-09",
                         "2024-01-11")),
  amount     = c(1200, 1100, 1300, 900, 1200, 850, 1450)
)

gappy

# A tibble: 7 × 2
  order_date amount
  <date>      <dbl>
1 2024-01-01   1200
2 2024-01-03   1100
3 2024-01-06   1300
4 2024-01-07    900
5 2024-01-08   1200
6 2024-01-09    850
7 2024-01-11   1450

If you reach for dplyr::lag() to compute a day-over-day change, you get the wrong answer:

gappy |>
  mutate(
    previous_row = lag(amount),
    change       = amount - previous_row
  )

# A tibble: 7 × 4
  order_date amount previous_row change
  <date>      <dbl>        <dbl>  <dbl>
1 2024-01-01   1200           NA     NA
2 2024-01-03   1100         1200   -100
3 2024-01-06   1300         1100    200
4 2024-01-07    900         1300   -400
5 2024-01-08   1200          900    300
6 2024-01-09    850         1200   -350
7 2024-01-11   1450          850    600

The row labelled 2024-01-03 reports a change against 2024-01-01. But those are not consecutive days — January 2nd is missing. lag() gives you the previous row, and you asked for the previous day. On a table with gaps those are different questions.

The problem compounds as the gaps widen. 2024-01-06 is compared against 2024-01-03, three days earlier.

What ti does instead

Every ti calculation begins by building a complete calendar for the requested time unit, then joining your data onto it. Missing days appear as rows with a zero value:

filled <- gappy |>
  dod(.date = order_date, .value = amount, calendar_type = "standard") |>
  calculate() |>
  collect() |>
  arrange(date)

filled |>
  select(date, amount, dod_amount, missing_date_indicator)

# A tibble: 11 × 4
   date                amount dod_amount missing_date_indicator
   <dttm>               <dbl>      <dbl>                  <dbl>
 1 2024-01-01 00:00:00   1200         NA                      0
 2 2024-01-02 00:00:00      0       1200                      1
 3 2024-01-03 00:00:00   1100          0                      0
 4 2024-01-04 00:00:00      0       1100                      1
 5 2024-01-05 00:00:00      0          0                      1
 6 2024-01-06 00:00:00   1300          0                      0
 7 2024-01-07 00:00:00    900       1300                      0
 8 2024-01-08 00:00:00   1200        900                      0
 9 2024-01-09 00:00:00    850       1200                      0
10 2024-01-10 00:00:00      0        850                      1
11 2024-01-11 00:00:00   1450          0                      0

Now January 2nd exists, holds a zero, and the day-over-day comparison lines up with the actual calendar.

The `missing_date_indicator` column

Zero-filling is useful for arithmetic and dangerous for interpretation. A day with no sales and a day with genuinely zero margin look identical once both are zeroes.

So ti labels them. Every completed calendar carries a missing_date_indicator column: 1 if the row was invented to fill a gap, 0 if it came from your data.

filled |>
  count(missing_date_indicator)

# A tibble: 2 × 2
  missing_date_indicator     n
                   <dbl> <int>
1                      0     7
2                      1     4

Use it to keep the two cases apart. To count only trading days:

filled |>
  filter(missing_date_indicator == 0) |>
  summarise(
    trading_days = n(),
    mean_amount  = mean(amount)
  )

# A tibble: 1 × 2
  trading_days mean_amount
         <int>       <dbl>
1            7       1143.

Compare that against the naive average over all rows, which is dragged toward zero by the padding:

filled |>
  summarise(
    all_rows    = n(),
    mean_amount = mean(amount)
  )

# A tibble: 1 × 2
  all_rows mean_amount
     <int>       <dbl>
1       11        727.

Both numbers are legitimate; they answer different questions. Averaging over the calendar tells you daily run rate including closures. Averaging over trading days tells you how a trading day performs. The indicator lets you choose deliberately rather than by accident.

What ti refuses to fill

Calendar completion is bounded. ti fills gaps inside the observed date range and does not extend beyond it. There is no back-filling to the start of the year and no padding forward to today.

filled |>
  summarise(first_row = min(date), last_row = max(date))

# A tibble: 1 × 2
  first_row           last_row           
  <dttm>              <dttm>             
1 2024-01-01 00:00:00 2024-01-11 00:00:00

The series runs from the first observed date to the last, and no further. Data you never had does not get invented.

This bounding is applied per group. Each group’s calendar is completed within that group’s own first and last observation, which matters whenever groups enter or leave at different times:

openings <- tibble::tibble(
  store      = c("A", "A", "A", "B", "B"),
  order_date = as.Date(c("2024-01-01", "2024-01-03", "2024-03-01",
                         "2024-03-01", "2024-03-05")),
  amount     = c(10, 20, 30, 40, 50)
)

openings |>
  group_by(store) |>
  ytd(.date = order_date, .value = amount, calendar_type = "standard") |>
  calculate() |>
  collect() |>
  summarise(first_date = min(date), last_date = max(date), n_rows = n(), .by = store) |>
  arrange(store)

# A tibble: 2 × 4
  store first_date          last_date           n_rows
  <chr> <dttm>              <dttm>               <int>
1 A     2024-01-01 00:00:00 2024-03-01 00:00:00     61
2 B     2024-03-01 00:00:00 2024-03-05 00:00:00      5

Store B opens in March and its series starts in March. It is not given two months of zeroes to match store A. A store that opened in March did not have a bad January — it had no January, and padding it would corrupt every average and year-over-year comparison that touches it.

Inspecting gaps before you calculate

A ti object reports what it found before you run anything. Printing one summarises the calendar it intends to build, including how many dates were missing:

gappy |>
  dod(.date = order_date, .value = amount, calendar_type = "standard")

── Day over day ────────────────────────────────────────────────────────────────

Function: `dod` was executed

── Description: ──

This creates a full day `sum()` of the previous day amount and compares it with
the full day `sum()` current day amount from the start of the standard calendar
day to the end of the day

── Calendar: ──

• The calendar aggregated order_date to the day time unit
• A standard calendar is created with 0 groups
• Calendar ranges from 2024-01-01 to 2024-01-11
• 3 days were missing and replaced with 0
• New date column date was created from order_date

── Actions: ──

✔Aggregate

✔Shift 1 day

✔Compare previous day

✖Proportion of Total

✖Count Distinct

── Next Steps: ──

• Use `calculate()` to return the results

────────────────────────────────────────────────────────────────────────────────

If the missing-date count is far higher than you expect, that is usually a signal about the data rather than the calculation — a wrong grouping column, a filter applied too early, or a date column that is not the one you meant.

A related tool: calendar attributes

augment_standard_calendar() solves a different problem and is worth knowing about. It does not fill gaps — it annotates the rows you already have with calendar context: period boundaries, day counts, and how far through each period a date sits.

augmented <- gappy |>
  augment_standard_calendar(.date = order_date) |>
  collect()

augmented |>
  select(order_date, month_end_date, days_complete_in_month,
         days_remaining_in_month, weekend_indicator) |>
  head(5)

# A tibble: 5 × 5
  order_date month_end_date days_complete_in_month days_remaining_in_month
  <date>     <date>         <drtn>                 <drtn>                 
1 2024-01-01 2024-01-31     0 days                 30 days                
2 2024-01-03 2024-01-31     2 days                 28 days                
3 2024-01-06 2024-01-31     5 days                 25 days                
4 2024-01-07 2024-01-31     6 days                 24 days                
5 2024-01-08 2024-01-31     7 days                 23 days                
# ℹ 1 more variable: weekend_indicator <dbl>

These columns are what you need for pacing and run-rate work — projecting a partial month to a full month, or explaining a shortfall by weekend placement rather than performance. The full set covers year, quarter, month and week boundaries, day-of-week and month labels, and complete/remaining day counts for each period.

Key points

dplyr::lag() gives you the previous row; period comparisons need the previous date. On gappy data these differ.
ti completes the calendar before calculating, so comparisons align to real dates.
missing_date_indicator marks invented rows — filter on it to separate closures from genuine zeroes.
Completion is bounded by the observed range and never extends past it.
Bounds are per group, so late-starting groups are not zero-padded backwards.
Print the object to see the missing-date count before running the query.