2 Replies Latest reply on Oct 27, 2016 8:36 AM by Paula Garzon

# Max Month on Filter

Hi community!

I have accumulated data by month for each year so for example as of 10/2016 I have all the data from 1/1 to 10/30/2016. I want to create a new view where I need to show the data as of 12/31 that means Month=12 for the past 3 years including current year (2016). However, for the current year I only have data for October so far. So, in the filter month I can't just check 10 and 12 because it will be double counting for the years that have data in October and December.

I want to calculate a field or do a calculated formula in the filter condition where Year=2014 show Month=12, Year=2015 show Month=12, Year=2016 show Month 10, but then next month it will show 11 and then by December it will show 12, so in other words I want to always show the Max of the Moth grouped by Year as a filter for my other calculations. I have tried to put a condition on the filter formula: Max(Month) = Max(Month) but it doesn't work. I don't know how to do it.

As of today:

Year:   2014  2015    2016

Month:    12       12       10

Hope you guys can help me!

Thank you!!

MP

• ###### 1. Re: Max Month on Filter

hi Paula,

So how's this for you?

I've first created a DATEPART for Year and Month (in Tableau 10 we can now nest these in a single formula...but have split out here to help explanation & I've done it on T9 - so can't use DATEPART/YEAR in the LoD Calculation).

Once I have these the following calculation will pick up the last month each year (and is fully dynamic as new data comes in).

[Last Month Each Year Filter]

[Date - Month]={FIXED [Date - Year]: MAX([Date - Month])}

Hope that does the trick, and makes sense...please post back if not (on either front!)

1 of 1 people found this helpful
• ###### 2. Re: Max Month on Filter

This is awesome! and worked like wonders. Thank you so much Simon