Creating Date Calculations for Specific Date Ranges

Published: 31 Jan 2013
Last Modified Date: 25 Aug 2023

Question

How to create calculated fields that can be used to filter out specific date ranges, such as the last n number of days, the previous week, or a specific range of dates.

Environment

Tableau Desktop

Answer

Use the following formulas as templates from which to create calculated fields. In the formulas below, Date is the name of the date dimension.

Filtering a Range of Days

Use the following formula to create a calculated filed that filters for the last n days:

IF [Date] < (today() - <n> + 1) OR [Date] > today() THEN NULL ELSE [Date] END

For example, a calculated field that filters data for the last 90 days would have the following formula:

IF [Date] < (today() - 90 + 1) OR [Date] > today() THEN NULL ELSE [Date] END

Formula for the last 7 days:

IF [Date] < (today() - 7 + 1) OR [Date] > today() THEN NULL ELSE [Date] END

Filtering Weeks

Use the following formula to filter data for the week before last:

IF [Date] > (today() - 8 + 1) OR [Date] <= (today() - 15 + 1)
THEN NULL ELSE [Date] END

Use the following formula to filter for the last eight weeks:

IF 
DATEPART('week', [Date]) <DATEPART('week', TODAY()) - 8 
OR 
DATEPART('week', [Date]) > DATEPART('week', TODAY()) -1 
THEN NULL 
ELSE [Date]
END

Additional Information

You can use a date parameter, another field, or a fixed date in place of TODAY() in order to base the calculation on a different anchor date.
The calculations can be modified with any of the date_part values listed in Date Functions to filter different date levels.

To view the above examples in action, see the video below.

Note: the Sample - Superstore data source's maximum date is January 6, 2016 for for the Shipping date dimension. This means that there we are not able to use TODAY() but we use a substitute.