Tableau - Date Calculations

Tableau - Adding Dates

To return a future or past date in Tableau use DATEADD


                    DATEADD(date_part, interval, start_date)
                     

The date_part can be:

  • day
  • week
  • month
  • quarter
  • year

Example

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:

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


                    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:

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


Dash-Intel is a Power BI and Tableau resource site for data visualization and building BI dashboards.

Data Analyst & Consultant

Copyright 2015-2023 Dash-Intel.com Terms