One of the most underrated functions in Tableau, in my opinion, is DATETRUNC. Underrated, underused, and not understood. Recently I was disappointed to read a whole book about Tableau in which it wasn’t mentioned even once.
Technically speaking, DATETRUNC “truncates” any date value to the starting point of the period: year, quarter, month, week, and so on. For example:
| NOW() | 29/08/2025 16:14:35 |
| DATETRUNC(“hour”, NOW()) | 29/08/2025 16:00:00 |
| DATETRUNC(“day”, NOW()) | 29/08/2025 00:00:00 |
| DATETRUNC(“week”, NOW(), “monday”) | 25/08/2025 00:00:00 |
| DATETRUNC(“month”, NOW()) | 01/08/2025 00:00:00 |
| DATETRUNC(“quarter”, NOW()) | 01/07/2025 00:00:00 |
| DATETRUNC(“year”, NOW()) | 01/01/2025 00:00:00 |
This is useful for calculations, but the real power comes from understanding that DATETRUNC is a hierarchical function, because it actually returns the parent of the date at the given level. So if we want to check if [date 1] and [date 2] are both in the same month – meaning that both have the same parent month – we can use DATETRUNC(“month”, [date 1]) = DATETRUNC(“month”, [date 2])
… instead of what I have seen too many times:
MONTH([date 1]) = MONTH([date 2]) AND YEAR([date 1]) = YEAR([date 2])
If we want to group dates by month, we can use DATETRUNC(“month”, [date]), and that’s it – we’ve created a parent field for [date] at the month level.
This is especially useful in creating relationships for date fields in data sources, for example when one table is at the timestamp level (transactional data) and the other at the quarterly level (quarterly goals). Just create a relationship calculation with DATETRUNC(“quarter”…) on both sides, and it works.
There are countless other examples, but the important thing is to understand the main idea: DATETRUNC doesn’t just change the date value, it raises it to a higher level in the hierarchy.
Now go out and use it.
BI Stories 
Leave a comment