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.

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

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