Dates are a very special class of Dimension – I mean real dates – not a string (text) that looks like a date or is coded to represent the date.  Dates self-sequence, and sort have a built-in calendar hierarchy and can be used as discrete or continuous  but most importantly, they can be used in date functions –  Pretty cool –

see the videos at Link to Dates Video

Just to be clear, dates in Tableau work the same way as those you used in a spreadsheet calculator.   They are based on a whole number that converts to a long past historic date.  The number increments by 1 for each day.  The decimal part of the number represents the fractional part of a 24-hour day.  The date hierarchy spans from Year down to Seconds.  

The structure is based on the Gregorian calendar.  3 date parts can be used in date functions that are not in the hierarchy –

Week – they don’t convert to months/years

Weekday – Sunday, Monday, etc.

Dayofyear – 1 on Jan 1 and 365 on Dec 31 ( 366 for leap years)

Date levels are used in calculations to identify the level you want you use in the formula.   For example, Datepart(‘year’,date) returns the integer value for the year – like 2022.  Datepart(‘month',date) returns 1 for January and 12 for December – you get it!

Date functions come in different varieties – like those that return a date – of September 4, 2022, or those that return a reference to a date “Monday”.  I like to group into 4 groups the date functions by what they return

Wow – That’s a lot of functions to remember – 

With practice you will find the ones you use the most and what they do – For now think about how you are going to use the value that is returned – If you need a real date the look to the Dates column,  if you want to find the same date next year – use a Date Math function and if you want a label something in the Reference to Date is the best choice

Now a look at syntax – it is pretty common to all –  The expression starts with the function name from the chart above, and next in quotes and lowercase is the date level you need – it is ALWAYS singular – i.e. ‘month’  not ‘months’-  then the date dimension from the data loaded and finally an adjustment for the week start – the default week is Sunday – Saturday but you may need Monday – Sunday and can make the adjust here to ‘monday’ 

Some of the most commonly used date functions include

Datename –  returns the text for the date level – like “January”  – good for charts and labels

Datepart –  returns the integer value relating to the date level – 1 for Jan 12 for Dec (note will aggregate in calculations

Datetrunc-  returns the actual date/time of the first date in the period of the date level

Today() (Sept 4, 2023) and Now() read your system clock to return the current date or date/time. 

They can be use alone or in combination with other date functions 

Note year returns an integer so subtracting 1 from the year of Today()  will return last year as an integer:

2023 sales = if YEAR([Order Date])=YEAR(TODAY()) then [Sales] end 

2022 sales = if YEAR([Order Date])=YEAR(TODAY())-1 then [Sales] end

Date math is another way to get similar values here done with datetrunc and dateadd – datetrunc returns the first date of the period (e.g. Jan 1) – and dateadd subtracts 1 from today() to return Sept 4, 2022

2023 sales = if datetrunc('year',[Order Date])=datetrunc('year',TODAY()) then [Sales] end

2022 sales = if datetrunc('year',[Order Date])=datetrunc('year',dateadd('year',-1,TODAY())) then [Sales] end

The result is the same.  The choice of method depends on the result you need as you will see in the next section on LODs

Datediff can be used to determine the difference between 2 dates. In Superstore data the Days to Ship can be found with:

datediff('day',[Order Date],[Ship Date])

But you need to understand that datediff is done with integer math – 

first, the datepart at the day level is determined as integer values for the 2 dates and then the difference between is integers is calculated.  The result is an integer (  4 not 4.5) – here the overall average of the at different ship modes is calculated

That will cover the basics now you need to practice – that’s how you will learn! –

For more advanced who need help doing YTD and YoY-type calculations – please see Link to YoY

If you are using Fiscal Years note:  Many date functions work only with Calendar dates – if you need YoY and YTD help with FY see Link to FY Metrics  the calculations are advanced but there is a link where you can download the formulas