DateShift

Time intelligenceCalculate modifier

DateShift(dateCol, n, unit)

A modifier for Calculate: evaluates the measure over data shifted by n units of "day" | "week" | "month" | "quarter" | "year" (negative = past), while the chart’s date buckets stay put — each bucket shows the shifted period’s value. Date predicates in the same Calculate pin the output buckets and are not shifted.

Computed in-database with a shifted join, correct at every grain including weeks.

When to use it

Year-over-year, quarter-over-quarter, month-over-month — the [Revenue] / [RevenueLY] / growth-% trio.
Any shifted baseline: same week last year for seasonality, prior month for run-rate checks.

Example

RevenueLY := Calculate([Revenue], DateShift(Date.Date, -1, "year"))

// Year-over-year growth
YoYGrowth := Divide([Revenue] - [RevenueLY], [RevenueLY])

Under the hood

The sibling CTE groups last year’s rows by their date displaced forward one year, so they join back onto this year’s buckets:

SquareX
Compiled SQL (simplified)

// On a line chart by month
RevenueLY := Calculate([Revenue], DateShift(Date.Date, -1, "year"))

WITH "__sqx_0" AS (
  SELECT DATE_TRUNC('month', "Sales"."sold_at" + INTERVAL '1 year') AS "k0",
         SUM("Sales"."amount") AS "val"
  FROM "sales" AS "Sales"
  GROUP BY 1
)
SELECT DATE_TRUNC('month', "Sales"."sold_at") AS "month",
       SUM("Sales"."amount") AS "Revenue",
       MAX("__sqx_0"."val")  AS "RevenueLY"
FROM "sales" AS "Sales"
LEFT JOIN "__sqx_0"
  ON "__sqx_0"."k0" = DATE_TRUNC('month', "Sales"."sold_at")
GROUP BY 1

Week grains stay correct because the shift happens on the raw date before bucketing — no ISO-week-boundary drift.

Good to know

Unlike YTD/RollingSum, DateShift composes with any measure — it shifts context rather than accumulating.

DateShift

When to use it

Example

Under the hood

Good to know

Related