Tableau Date Functions Returning Wrong Results: Truncation and LOD Traps

Your Tableau date chart looked fine in development, but now the year-over-year numbers don't match the source data. You've double-checked the field types, the filters look correct, and yet something is clearly off. The problem is almost never a bug in Tableau — it's a handful of repeatable traps in how date functions interact with aggregation and Level-of-Detail expressions.

This article walks through the most common ways Tableau date calculations go wrong and gives you concrete fixes for each one.

What you'll learn

• How DATETRUNC actually works and why it produces unexpected groupings
• Why DATEDIFF counts boundaries, not durations, and when that bites you
• How LOD expressions interact with date truncation in ways that silently break aggregations
• How fiscal year offsets shift your data in ways standard date parts don't warn you about
• A repeatable debugging workflow to isolate date calculation problems

Prerequisites

You should be comfortable building calculated fields in Tableau Desktop or Tableau Cloud. The examples assume you're on a reasonably current version (2021.x or later). Some LOD behavior changed in earlier releases, so if you're on an older version, verify against your own environment.

How DATETRUNC Actually Works

DATETRUNC rounds a date down to the start of the specified period. That sounds obvious, but the consequences catch people off guard. When you write DATETRUNC('month', [Order Date]), Tableau doesn't just extract the month — it returns a full date value set to the first day of that month at midnight.

This matters because the result is still a date, not an integer. If you then wrap it in another function expecting a number, or drag it onto a shelf as a continuous dimension, Tableau renders it differently depending on context.

DATETRUNC('month', #2024-03-15#)  →  #2024-03-01 00:00:00#
DATETRUNC('quarter', #2024-03-15#)  →  #2024-01-01 00:00:00#
DATETRUNC('week', #2024-03-15#)  →  #2024-03-10 00:00:00#  (week starts Sunday by default)

The week-start default is where people get burned. Tableau uses Sunday as the first day of the week unless you pass an explicit week_start parameter or change the locale. If your business defines a Monday-to-Sunday week, your groupings will be off by one day for rows that fall on Sundays.

The fix for week-start mismatches

Pass the optional third argument to control the start day:

DATETRUNC('week', [Order Date], 'monday')

Alternatively, go to Data > Date Properties for the data source and set the week start there. That applies globally to all week-level calculations in the workbook, which is usually what you want.

DATEDIFF Counts Boundaries, Not Durations

This is the most common source of off-by-one errors in Tableau date math. DATEDIFF counts how many date-part boundaries are crossed between two dates, not the actual elapsed time in that unit.

DATEDIFF('month', #2024-01-31#, #2024-02-01#)  →  1
DATEDIFF('month', #2024-01-01#, #2024-01-31#)  →  0
DATEDIFF('year',  #2023-12-31#, #2024-01-01#)  →  1

The second example surprises people the most. January 1 to January 31 is 30 days, but DATEDIFF('month', ...) returns 0 because no month boundary was crossed. If you're trying to calculate