Date calculations

Parts versus values

The single most useful distinction in Tableau dates.

DATEPART returns a number — DATEPART('month', [Order Date]) gives 3 for March. On a shelf as a discrete date part, MONTH(Order Date) gives twelve headers with every year pooled — the seasonality view.

DATETRUNC returns a date rounded down — DATETRUNC('month', [Order Date]) gives 1 March 2024. As a continuous date value, it keeps its position on the timeline — the trend view.

Which you want

Seasonality across years → discrete date part. Trend over time → continuous date value. Choosing wrong is why a chart sometimes shows twelve bars when you expected a line.

Differences and shifts

DATEDIFF('day', [Order Date], [Ship Date])       -- days between
DATEADD('month', 4, [Order Date])                 -- shift forward
DATEADD('day', -7, TODAY())                       -- a week ago

DATEDIFF counts boundaries crossed, not elapsed time. DATEDIFF('year', '2023-12-31', '2024-01-01') returns 1, despite being one day apart. When you want elapsed duration, difference in days and divide.

Now and today

TODAY() returns the date; NOW() returns date and time. Both evaluate on refresh rather than continuously.

With a live connection they usually resolve to the database server's clock; on an extract, to the machine that refreshed it. That matters when systems sit in different time zones.

Relative comparisons

// flag the last 90 days
[Order Date] >= DATEADD('day', -90, TODAY())

// same period last year
DATEADD('year', -1, [Order Date])

// working days between two dates, excluding weekends
DATEDIFF('day', [Start], [End])
  - (DATEDIFF('week', [Start], [End]) * 2)

That last one is the standard business-days calculation. It handles weekends but not public holidays — those need a joined holiday table.

Fiscal years

Right-click a date field → Default Properties → Fiscal Year Start to set the month your year begins. DATETRUNC('year', ...) then respects it, which saves writing offset logic by hand.