Tableau Date Calculations Guide

Date calculations can be tricky until we develop the intuition for how date data can be manipulated. Being able to manipulate data across time is essential for analysis & reporting. There are different ways to achieve the same things, but below is what I’ve found useful & most intuitive.

Intuition

Time is relative – all date calculations are relative to some fixed point in time.

We first define a fixed point in time, then we write all the date calculations relative to that point in time.

In my experience, there are two commonly used fixed points of time:

1. Today
2. The last date in a dataset

With this fixed point in time, we can then define periods of time relative to that moment. For example, relative to today, we can define yesterday or relative to this year, we can define last year. Then we can start building calculations that compute differences day over day, year over year, or whatever else we might care about.

Fixed point in time

I typically use the last date in a dataset, but using today’s date works just fine too. Depending on the dataset, the intuition would be the same, but the calculations might need slight tweaks. Everything below will use the last date in a dataset.

To define the last point in a Tableau dataset, we can use a level of detail calculation.

Max Date

{ FIXED : MAX([Date])}

This calculation looks across the entire dataset, and returns the last (i.e. max) date value regardless of other dimensions/filters being used. With this one date value, we now can define periods of time relative to it.

Basic time periods

Using the Max Date field we can now start calculating time periods.

Last 7 Days

DATEDIFF("day", [Date], [Max Date]) < 7

The field computes the difference in days between each date value and the max date value. We then compare those values to 7, and only return true if the difference is less than 7.

We could then apply the new Last 7 Days field as a filter to a view, or use it within further calculated fields.

Last Week

DATEDIFF("week", [Date], [Max Date]) = 1

The field computes the difference in weeks between each date value and the max date value. We then compare those values to 1, and only return true if the difference is exactly 1.

Current Month (MTD)

DATEDIFF("month", [Date], [Max Date]) = 0

The field computes the difference in months between each date value and the max date value. We then compare those values to 0, and only return true if the difference is exactly 0.

We can now capture almost any period in time with this logic, returning true for the period we want, and false for everything else.

DATEDIFF("date_part", [Date], [Max Date]) compared to #

Combine basic time periods with a parameter

We can combine different time periods into one field using a parameter. Then users can dynamically select different time periods and the data in the view will filter to that period once we’ve applied the new field as a filter.

Here’s an example parameter we can make.

Next, we can use this parameter in a calculated field. By using multiple CASE WHEN statements, depending on what value a user has selected in the parameter, the specified time period will be used as a filter.

CASE [Parameters].[Time Period]
WHEN "Last 7 Days" THEN DATEDIFF("day", [Date], [Max Date]) < 7
WHEN "Last 28 Days" THEN DATEDIFF("day", [Date], [Max Date]) < 28
WHEN "Last Week" THEN DATEDIFF("week", [Date], [Max Date]) = 1
WHEN "Last Month" THEN DATEDIFF("month", [Date], [Max Date]) = 1
WHEN "MTD" THEN DATEDIFF("month", [Date], [Max Date]) = 0
WHEN "YTD" THEN DATEDIFF("year", [Date], [Max Date]) = 0
WHEN "All Time" THEN TRUE
END

Now we can apply the filter (setting it to true) and then the dates will dynamically update with the parameter value selected.

Time period comparisons

To compare one period to another, we need to define both periods. Typically, we select a main period and then want a comparison period based on certain factors related to the main period.

For example, our main period could be the previous month. We might want to compare that period against the same month for the previous year, or compare that main period against the month prior. Each comparison period being relative to the main period.

There are some general steps to accomplish our goal dynamically:

1. Dynamically define the beginning and end of the main period
2. Define comparison periods, relative to the main period
3. Write a final calculation that only includes the main & comparison periods (excluding all other periods)

Let’s say our [Main Period] calculation is setup like this:

First, we dynamically define the beginning & end of the main period.

Main Period Start

• Return the first (min) date in the main time period

{ FIXED : MIN(IF [Main Period] THEN [Date] END)}

Main Period End

• Return the last (max) date in the main time period

{ FIXED : MAX(IF [Main Period] THEN [Date] END)}

Next, we define comparison periods relative to the main period by using the calculations above.

Previous Period

• The previous period should start right before the main period, and extend back as far as the main period, in terms of days (if the main period = “last 7 days” then the previous period should be the 7 days prior to that)
• First part defines where the previous period should start (day just prior to the start of the main period)
• Second part defines how far back the previous period should extend (as many days as in the main period)

DATEDIFF("day", [Date], [Main Period Start]) > 0 AND
DATEDIFF("day", [Date], [Main Period Start]) <= DATEDIFF("day", [Main Period Start], [Main Period End]) + 1

Previous Year

• We define the start and end of the previous year, then only include dates in that date range
• First part defines the start of the previous year as the same date as the main period start, but subtracting one year
• Second part defines the end of the previous year as the same date as the main period end, but subtracting one year

[Date] >= DATEADD("year", -1, [Main Period Start]) AND 
[Date] <= DATEADD("year", -1, [Main Period End])

Previous Year (Weekday)

• We define the previous year, but align the weekdays year over year such that each weekday of the main period aligns with weekdays of the previous year
• First part defines the start of the previous year as the same date as the main period start, but subtracting 364 days
• First part defines the end of the previous year as the same date as the main period end, but subtracting 364 days

[Date] >= { FIXED : MAX(IF [Date] = DATEADD("day", -364, [Main Period Start]) THEN [Date] END)} AND
[Date] <= { FIXED : MAX(IF [Date] = DATEADD("day", -364, [Main Period End]) THEN [Date] END)}

Comparison Period

We combine all the fields into a calculated field, using a parameter to select the different periods.

Main or Comparison Period

• Combine the [Main Period] and [Comparison Period] fields into one field that is used as a filter, so only the specified dates are included
• We want both periods, so we use the “or” operator

[Main Period] OR [Comparison Period]

Time period comparisons for visualizations

A

Dynamic date granularity

A