## Tableau - Date Calculations

To return a future or past date in Tableau use DATEADD




The date_part can be:

• day
• week
• month
• quarter
• year

### Example




Find the date 7 days ago




Some tips:

• Change data type to date
• Change data type to discrete

These calculations come in handy when you need to look at project deadlines, forecasts or look back at advertising spend from 7 days ago to calculate ROI.

## Tableau - Date Differences

Find the difference between two dates using DATEDIFF


DATEDIFF(date_part, start_date, end_date, [start_of_week])


The date_part can be:

• day
• week
• month
• quarter
• year

[start_of_week] is an optional parameter which can be any day of the week (i.e "Monday", "Tuesday", etc)

### Example

Find the number of months between two specified dates


DATEDIFF('month',#2017-07-01#, #2017-09-01#)


Find the number of days between two specified dates


DATEDIFF('day',#2017-01-01# , TODAY())


Some tips:

• Change data type to date
• Change data type to discrete

DATEDIFF with DATETRUNC lets you track date differences without hard coding a specific date.

## Tableau - Date Truncation

Return the first day of the week, month, quarter or year using DATETRUNC


DATETRUNC(date_part, date, [start_of_week])


The date_part can be:

• day
• week
• month
• quarter
• year

[start_of_week] is an optional parameter which can be any day of the week (i.e "Monday", "Tuesday", etc)

### Example

Return the first day of this year


DATETRUNC('year', TODAY())


Return the first day of the quarter


DATETRUNC('quarter', TODAY())


Return the first day of the month one year ago


DATETRUNC('month', TODAY() -365)


Some tips:

• Convert to an exact date
• Change data type to discrete

These calculations come in handy when finding the number of days in the month, quarter or year dynamically.

## Elapsed Number of Days in Month, QTR, Year Dynamically

To get the number of days elapsed in the current month create the following calculated field:


DATEDIFF('day',DATETRUNC('month',today()),today())


To get the number of days elapsed in the current quarter create the following calculated field:


DATEDIFF('day',DATETRUNC('quarter',today()),today())


To get the number of days elapsed in the current year create the following calculated field:


DATEDIFF('day',DATETRUNC('year',today()),today())


## Total Number of days in any given month

To get the number of days in the current month dynamically:




Some uses: Track goals and targets. For instance, let's say your sales goal is \$10,000 per month. If you are 23 days into the month then you can use the formulas above to see where you should be in terms of the number of days that elapsed in the month.

Some tips:

• Aggregrate the measure as a minimum not a sum
• Change to discrete to show numeric values or drag to text
• This will depend on the filter context

The latter calculation depends on the filter context which is a fancy way of saying it depends on what dimensions/measures are in the row or column shelf in tableau.

• SEO