To return a future or past date in Tableau use DATEADD
DATEADD(date_part, interval, start_date)
The date_part can be:
Add 1 month to 2017-09-01
DATEADD('month', 1, #2017-09-01#)
Find the date 7 days ago
DATEADD('day', -7, TODAY())
Some tips:
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.
Find the difference between two dates using DATEDIFF
DATEDIFF(date_part, start_date, end_date, [start_of_week])
The date_part can be:
[start_of_week] is an optional parameter which can be any day of the week (i.e "Monday", "Tuesday", etc)
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:
DATEDIFF with DATETRUNC lets you track date differences without hard coding a specific date.
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:
[start_of_week] is an optional parameter which can be any day of the week (i.e "Monday", "Tuesday", etc)
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:
These calculations come in handy when finding the number of days in the month, quarter or 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())
To get the number of days in the current month dynamically:
DATEPART('day', DATEADD('day', -1, DATEADD('month', 1, DATETRUNC('month', [Date]))))
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:
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.