Dynamically Group Dates by Month Level in Most Recent Quarter or by Quarter Level

Published: 19 Sep 2017
Last Modified Date: 13 Nov 2017


How to dynamically group dates by month level in most recent quarter or by quarter level.  For example, the final view may show October, November, and December of 2014, as well as Q1, Q2 and Q3 of 2014.

User-added image


Tableau Desktop 9 and later versions


Create and name a calculated field as "Date Groups" and enter a formula similar to the following:
IF DATETRUNC( 'quarter', { FIXED : MAX( [Date] ) } ) = DATETRUNC( 'quarter', [Date] )
THEN DATENAME( 'month', [Date] ) + " " + STR( YEAR( [Date] ))
ELSE "Qtr " + STR( DATEPART( 'quarter', [Date] )) + " " + STR( YEAR( [Date] ))

In the above calculation, if the first day of the quarter that contains the most recent date in the data set is the same as the first day of the quarter of [Date] then the calculation returns the name of the month and the year as a string.

If the first conditional statement is not true then the calculated field will return the quarter that the date belongs to.
Did this article resolve the issue?