Tableau Filter Date By Formula: Month of most recent Year

Question

I have many years of data ingested into a Tableau workbook. I have dropped the [Date] pill into the "Filters" pane. When I edit the filter, I would like to apply the condition where I filter to the October of the most recent year.

Since the filter requires a Boolean, I have tried various combinations of

DATEPART('month',[Date]) = 10 AND DATEPART('year', MAX([Date]))

But I can't seem to get around mixing the max aggregation. I have tried wrapping the Max function with ATTR DATEPART('year', ATTR(MAX([Date])), but this doesn't seem work in the Filter>By formula (ATTR is not recognized). Wrapping the max function with {}, I get the error "The formula must be an aggregate calculation or refer only to this field.

Answer 1

In order to filter dates for the most recent year, you need a calculated field like this:

if year([Order Date]) = year({ FIXED : MAX([Order Date])}) then 'ok' else 'ko' end

Basically you compare ther year for each date to the year of the max date (using fixed in order to bypass filters).

Doing so you will filter (selecting 'ok') just rows for your max year and then you only need to add another quick filter based on month(Date) in discrete mode (blue).

You should get something like this: