Aggregate values according to date granularity

Question

I am facing an issue I can't solve by my own.

I am working with time series. So I have date in column. The granularity can be YEAR, QUARTER & MONTH.

I wanted to know how to display the SUM of the LASTEST MONTH of the selected date granularity.

For example:

If we are displaying yearly data, I would like to see, for each year, the SUM of DECEMBER data

If we are displaying quarterly data, I would like to the SUM of MARCH data for Q1, The SUM of JUNE data for Q2 and so on.

Thank you in advance for your help and feel free to ask any question if it's not clear ! :)

Regards,

Maxime

Answer 1

Replicating it on sample superstore

Let's assume your desired measure is [sales] . Create a CF like this

if month([Ship Date]) =
{EXCLUDE MONTH([Ship Date]) : max( MONTH([Ship Date]))}
then [Sales] END

Add this field instead of your chosen measure on the view and Voila.

---

As required in the comments, if only months at quarter ends are required, use this calculation field instead

if month([Ship Date]) =
{EXCLUDE MONTH([Ship Date]) : max( MONTH([Ship Date]))} 
and month([Ship Date]) IN (3, 6, 9, 12)
then [Sales] END

Check your Tableau version. Tableau introduced IN in its very later version.