By: Eric Parker
Eric Parker lives in Seattle and has been teaching Tableau and Alteryx since 2014. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email. You can also sign up for a Tableau Office Hour to work with him directly!
Tableau provides flexibility when working with dates, but sometimes getting it to do exactly what you want requires a custom touch.
If you’ve ever gotten stuck writing date calculations in Tableau, this webinar is for you!
Here’s an overview of what we covered.
Year to Date vs. Previous Year to Date
Month to Date vs. Previous Month to Date
Week to Date vs. Previous Week to Date
Quarter to Date vs. Previous Quarter to Date
Month over Month Comparisons
Comparing Running Year Progress
Keeping Data only From the Latest Date (in a data set)
Filtering Out Dates with Table Calculations
You can access and download the workbook I used for the demonstrations here.
Please check out our upcoming classes and office hours if you are looking to dive even deeper!
Here’s a list of the calculations used in the webinar (roughly) in order.
YTD Sales:
IF DATEDIFF('year',[Date],TODAY()) = 0
//AND [Date] <= TODAY()
THEN [Sales]
END
PYTD Sales:
IF DATEDIFF('year',[Date],TODAY()) = 1
AND DATEPART('dayofyear',[Date]) <= DATEPART('dayofyear',TODAY())
THEN [Sales]
END
YTD Sales Difference:
SUM([YTD Sales]) - SUM([PYTD Sales])
Day of Year:
DATEPART('dayofyear',[Date])
MTD Sales:
IF DATEDIFF('month',[Date],TODAY()) = 0
THEN [Sales]
END
PMTD Sales:
IF DATEDIFF('month',[Date],TODAY()) = 1
AND DAY([Date]) <= DAY(TODAY())
THEN [Sales]
END
WTD Sales:
IF DATEDIFF('week',[Date],TODAY()) = 0
THEN [Sales]
END
PWTD Sales:
IF DATEDIFF('week',[Date],TODAY()) = 1
AND DATEPART('weekday',[Date]) <= DATEPART('weekday',TODAY())
THEN [Sales]
END
YoY Monthly Sales % Diff:
(ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -12))
/
ABS(LOOKUP(ZN(SUM([Sales])), -12))
Latest Date?:
[Date] = {MAX([Date])}
QTD_Sales:
IF DATEDIFF('quarter',[Date],TODAY()) = 0
THEN [Sales]
END
PQTD_Sales:
IF DATEDIFF('quarter',[Date],TODAY()) = 1
AND [Date] <= DATEADD('month',-3,TODAY())
THEN [Sales]
END
PYTD Sales - Leap Year Alternate: (more on this here)
IF DATEDIFF('year',[Date],TODAY()) = 1
//Data has to be from last year
AND
(
IF YEAR(TODAY()) = 2020 THEN DATEPART('dayofyear',[Date]) <= (DATEPART('dayofyear',TODAY())-1)
//If it is 2020, we need to backtrack one day in our comparisons so we aren't comparing Jan 1 - Mar 14 to Jan 1 - Mar 15
ELSEIF YEAR(DATEADD('year',-1,TODAY())) = 2020 THEN DATEPART('dayofyear',[Date]) <= (DATEPART('dayofyear',TODAY())+1)
//If last year was 2020 we need to add one day in our comparisons so we aren't comparing Jan 1 - Mar 14 to Jan 1 - Mar 13
ELSE DATEPART('dayofyear',[Date]) <= DATEPART('dayofyear',TODAY())
//If 2020 is not in the equation, the data should work like a normal YTD comparison.
END
)
THEN [Sales]
END
“Month Filter”:
LOOKUP(MAX(DATETRUNC('month', [Date])),0)