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