KNOWLEDGE BASE

## Creating Date Calculations for Specific Date Ranges

Published: 31 Jan 2013

### 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

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

CLICK TO EXPAND SOLUTION

### 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`

CLICK TO EXPAND SOLUTION

### 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```

• 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.