LOD with date dimensions in Tableau - Step-by-Step Guide

Introduction

This feature helps you calculate values for specific dates or date parts, even when your view shows different levels of detail. It solves the problem of getting accurate totals or averages for dates regardless of how you slice your data.

When you want to find total sales per month even if your view shows daily sales.

When you need to compare yearly averages but your dashboard shows data by week.

When you want to fix a calculation at the quarter level while exploring daily data.

When you want to count unique customers per year regardless of the current view.

When you want to show the first purchase date per customer even if the view is by product.

Steps

Step 1: Open

- Tableau Desktop and connect to your data source

Your data fields appear in the Data pane

Step 2: Right-click

- in the Data pane on an empty space and select Create > Calculated Field

A calculation editor window opens

Step 3: Type

- the LOD expression using date dimensions, for example: { FIXED YEAR([Order Date]) : SUM([Sales]) }

The calculation is ready to fix sales by year regardless of the view

Step 4: Click

- OK to save the calculated field

The new field appears in the Data pane

Step 5: Drag

- the new calculated field onto Rows or Columns shelf or into the view

The view updates showing sales fixed by the year of the order date

Step 6: Change

- the date dimension in the view (e.g., from day to month)

The LOD calculation remains fixed at the year level, not changing with the view

Before vs After

Before

View shows daily sales totals, and totals change when filtering by month or quarter

After

View shows sales totals fixed by year, totals stay the same even when filtering by month or quarter

Settings Reference

Calculation Editor

📍 Right-click Data pane > Create > Calculated Field

To write and save LOD expressions using date dimensions

Default: Empty

Date Part Functions

📍 Calculation Editor toolbar or type manually

To extract parts of a date for use in LOD calculations

Default: None

Aggregation Functions

📍 Calculation Editor

To aggregate data inside the LOD expression

Default: None

Common Mistakes

Using a date field directly inside LOD without extracting a date part

The calculation may fix at the exact date level, causing unexpected results when the view changes

Use date functions like YEAR() or MONTH() inside the LOD to fix the calculation at the desired date level

Not using FIXED keyword and expecting the calculation to ignore view filters

Other LOD types like INCLUDE or EXCLUDE depend on the view and may not fix the date level as intended

Use FIXED LOD expressions to lock calculations at specific date dimensions regardless of the view

Summary

LOD expressions let you fix calculations at specific date levels like year or month.

Use date functions inside LOD to control the date granularity.

FIXED LOD keeps calculations stable even when the view changes date levels.