Date calculations in Tableau

by Ollie Matthews

I’ve found myself spending rather a lot of time on date calculations during our last two client projects, so I though it might be useful to make a reference post. The date functions in Tableau often do not make for the most elegant calculations. Be warned.

(All examples below use the sample superstore dataset)

Current calendar month sales:

IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=0

THEN [sales]

END

Previous calendar month sales:

IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=1

THEN [sales]

END

Current calendar months sales in the previous year:

IF DATETRUNC('month',[Order Date] = DATEADD('year',-1,DATETRUNC('month', {MAX(DATETRUNC('month',[Order Date]))})=1

THEN [Sales]

END

Sales, current year:

IF DATEDIFF('year',DATETRUNC('year',[Order Date]),{MAX(DATETRUNC('year',[Order Date]))})=0

THEN [sales]

END

Sales, previous 30 days:

IF DATEDIFF('day',DATETRUNC('day',[Order Date]),{MAX(DATETRUNC('day',[Order Date]))})<=30

THEN [Sales]

END

Avatar

Ollie Matthews

Fri 03 Sep 2021

Thu 12 Aug 2021