6 Replies Latest reply on May 10, 2018 2:35 PM by Gerardo Varela

# Calculated Field for Closest Date before Today()

Hi all, I'm struggling with how to create a calculated field so that I can filter on the single date in my 'Due Date' dimension that is closest to being before Today().

Attached a sample packaged workbook.

I did figure out how to create the calculated field for only showing due dates that have happened (e.g. filtering out all future due dates), but for some reason the next step of honing in on the max of that isn't working for me. I created 'Max Due Date Passed', which literally just takes the max of the due dates that have passed, but it doesn't give me the single most recent date.

• ###### 1. Re: Calculated Field for Closest Date before Today()

Hi Beatrix,

If you drop your Max Due date onto the worksheet by itself it'll give you the correct date.  So you're logic is there you just have to wrap it in a fix expression.

{FIXED:MAX(IF [Due Date] <= TODAY() THEN [Due Date]

END)}

Overview: Level of Detail Expressions

What does Tableau 9.1 mean for your LOD expressions? | Bora Beran

See attached!

Regards,

Gerardo

1 of 1 people found this helpful
• ###### 2. Re: Calculated Field for Closest Date before Today()

Thank you very much Gerardo! I think I was just pulling things into the table the wrong way so I wasn't seeing it clearly.

• ###### 3. Re: Calculated Field for Closest Date before Today()

I did have a follow-up question - what if I want to use that max due date as a filter? Right now it automatically makes the new max value the same for all rows for everything, regardless of it's due date, but my goal would be to use that so that I can filter on only those rows that have that max due date = actual due date.

Say if I drag 'New Timeframe' into the rows and then put 'Max Due Date Passed' into the filter - it doesn't seem to filter on only the 'New Timeframe' that had the max due date as their actual due date.

Hopefully I'm making sense, I feel like there are so many due date terms that it can get a bit confusing.

• ###### 4. Re: Calculated Field for Closest Date before Today()

Hi Beatrix,

[New Max]=[Due Date]

Set that as a filter and select true.  You'll have the results your after.

Regards,

Gerardo

1 of 1 people found this helpful
• ###### 5. Re: Calculated Field for Closest Date before Today()

Ha - it would be that simple!

I'm over here overthinking this and trying crazy things in my calculated fields.

Thank you so much Gerardo!!

• ###### 6. Re: Calculated Field for Closest Date before Today()

You're welcome!

Regards,

Gerardo