Date calculations in Tableau are deceptively complex. The built-in date functions cover common cases, but fiscal year adjustments, custom period comparisons, and week-over-week calculations require understanding how Tableau handles dates at the row level, the aggregate level, and inside LOD expressions. This guide covers the patterns that come up most often in production analytical environments.
Date calculations are among the most common sources of subtle errors in Tableau dashboards. The functions work as documented, but the interactions between date truncation, date arithmetic, fiscal year adjustments, and Tableau's internal date handling produce results that frequently do not match what the analyst intended.
Understanding the mechanisms prevents the bugs that are most difficult to find after the dashboard is in production — the ones where the numbers are plausible but wrong.
DATE vs DATETIME vs DATEPART
Tableau distinguishes between dates (DATE type, storing year/month/day) and datetimes (DATETIME type, storing year/month/day/hour/minute/second). Most analytical use cases work with dates rather than datetimes, but connecting to source tables with DATETIME columns requires explicit handling.
The most common issue: comparing a DATETIME column to a DATE value. If order_date is stored as a DATETIME (e.g., 2024-03-15 14:32:00) and you filter WHERE order_date = #2024-03-15#, Tableau compares the full datetime to midnight on that date and returns no results. Use DATETRUNC('day', [order_date]) = #2024-03-15# to truncate the time component before comparison.
DATEPART extracts a specific component of a date as an integer:
DATEPART('year', [order_date]) -- Returns 2024
DATEPART('month', [order_date]) -- Returns 1-12
DATEPART('quarter', [order_date]) -- Returns 1-4
DATEPART('week', [order_date]) -- Returns 1-53 (week of year)
DATEPART('weekday', [order_date]) -- Returns 1-7 (1=Sunday by default)
DATEPART is row-level — it returns an integer for each row's date value. It is the building block for date-based categorisations and groupings.
DATETRUNC: The Foundation of Period Aggregation
DATETRUNC returns the date truncated to the specified period — the first day of the year, month, quarter, or week that contains the input date.
DATETRUNC('month', #2024-03-15#) -- Returns #2024-03-01#
DATETRUNC('quarter', #2024-03-15#) -- Returns #2024-01-01#
DATETRUNC('year', #2024-03-15#) -- Returns #2024-01-01#
DATETRUNC is the canonical way to aggregate daily data to monthly or quarterly periods without using the built-in date hierarchy. By placing DATETRUNC('[Order Date]', 'month') on the Columns shelf as a dimension, you create a monthly axis without the table calculation complexity of the built-in date level controls.
The week truncation behaviour depends on the start-of-week setting. By default, Tableau treats Sunday as the first day of the week. If your organisation uses Monday as the first day:
DATETRUNC('week', [order_date], 'Monday')
This returns the most recent Monday on or before the input date.
DATEDIFF: Elapsed Time Between Dates
DATEDIFF returns the number of complete intervals of the specified type between two dates:
DATEDIFF('day', [start_date], [end_date]) -- Days between dates
DATEDIFF('month', [start_date], [end_date]) -- Complete months
DATEDIFF('year', [start_date], [end_date]) -- Complete years
A critical subtlety: DATEDIFF counts interval boundaries crossed, not time elapsed. DATEDIFF('month', #2024-01-31#, #2024-02-01#) returns 1 (one month boundary crossed: Jan → Feb), even though only one day has elapsed. DATEDIFF('month', #2024-01-01#, #2024-01-31#) returns 0 (no month boundary crossed), even though 30 days have elapsed.
For day counts, DATEDIFF('day') is reliable — it counts calendar days. For month and year calculations, be explicit about whether you want boundary-crossing counts or full-period elapsed time.
DATEADD: Moving a Date Forward or Backward
DATEADD adds a specified number of intervals to a date:
DATEADD('day', 7, [order_date]) -- 7 days after order_date
DATEADD('month', -1, [order_date]) -- 1 month before order_date
DATEADD('year', 1, [order_date]) -- 1 year after order_date
DATEADD is used for period-over-period calculations. To get the prior year equivalent of the current selected date:
DATEADD('year', -1, [Order Date])
Combined with DATETRUNC, you can compute the first day of the prior year:
DATEADD('year', -1, DATETRUNC('year', [Order Date]))
Fiscal Year Adjustments
Tableau's built-in date hierarchy uses the calendar year by default. For organisations with fiscal years that do not align with the calendar year (common in retail, where fiscal year might start February 1, or in many public companies where fiscal year starts July 1), you need custom fiscal period calculations.
The standard approach for fiscal year calculations:
**Fiscal year**: calculate the fiscal year number based on the calendar date.
For a fiscal year starting July 1:
IF DATEPART('month', [order_date]) >= 7
THEN DATEPART('year', [order_date])
ELSE DATEPART('year', [order_date]) - 1
END
This returns the fiscal year as the calendar year in which the fiscal year starts. Fiscal year 2024 runs July 1, 2024 through June 30, 2025.
**Fiscal quarter**: calculate the fiscal quarter number (1-4) from the calendar month.
For a July-start fiscal year:
INT((((DATEPART('month', [order_date]) - 7) + 12) % 12) / 3) + 1
This maps July, August, September to Q1; October, November, December to Q2; January, February, March to Q3; April, May, June to Q4.
**Fiscal month**: the month number within the fiscal year (1-12) follows the same offset logic.
Period-over-Period Comparisons With Date Parameters
The standard period-over-period comparison pattern uses two date parameters — Selected Start Date and Selected End Date — and calculates the equivalent prior period range from them.
Calculate prior period start:
DATEADD('day', -DATEDIFF('day', [Selected Start Date], [Selected End Date]) - 1, [Selected Start Date])
This gives the start of a period the same length as the selected period, ending the day before the selected period starts.
The comparison calculated field:
IF [order_date] >= [Selected Start Date] AND [order_date] <= [Selected End Date] THEN 'Current'
ELSEIF [order_date] >= {calculated prior start} AND [order_date] < [Selected Start Date] THEN 'Prior'
ELSE NULL
END
Use this as a colour dimension to compare current and prior periods on the same chart.
Week-over-Week and Day-of-Week Handling
Week-over-week comparisons require matching by day of week rather than by calendar day to avoid comparing, for example, the Monday revenue of one week to the Tuesday revenue of another.
The key calculation: what day of week is the current date, and what was the same day of week in the prior week?
DATEADD('week', -1, [order_date])
This gives the same day of week, one week prior. For YoY same-day-of-week comparison:
DATEADD('week', -52, [order_date])
This gives the same day of week, 52 weeks (364 days) prior — not exactly one year ago, but the same day of week in approximately the same calendar week one year ago.
For day-of-week filtering (show only weekdays, exclude weekends):
DATEPART('weekday', [order_date]) NOT IN (1, 7) -- Exclude Sunday (1) and Saturday (7)
LOD Expressions With Dates
Date calculations inside LOD expressions require attention to the grain at which the LOD is computed.
First order date per customer:
{ FIXED [Customer ID] : MIN([Order Date]) }
Days since first order (customer tenure), computable on any row:
DATEDIFF('day', { FIXED [Customer ID] : MIN([Order Date]) }, [Order Date])
This computes customer tenure at the order level — how many days after their first order was each subsequent order placed? Useful for cohort-based analysis.
Our Tableau consulting practice engineers complex analytical calculations — contact us to discuss Tableau development requirements for your analytics environment.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →