9 Replies Latest reply on Jun 18, 2018 9:34 AM by Joe Oppelt

# only select End of month date

Hi Guys

how can i do a filter which will only allow me to pick the last working day of the month only?

i have attached sheet with example data

can any one help me?

Thank you

• ###### 1. Re: only select End of month date

Define "last working day".

Do you mean the last CALENDAR day?

Or the last date in a given month that you have an actual row in the data source?

(For instance, June 30  might be the last calendar day of June, but you might only have data up through June 18.)

The last day of March was March 31, but it was a Saturday, and you might not have had business on Saturday, so your last data date might be March 30.

Depending on what you really need, you might have to do a different approach to the question.

• ###### 2. Re: only select End of month date

last working day means the last week day of the month not weekend

hope this helps

• ###### 3. Re: only select End of month date

Hi Imran,

Please see attached workbook which appears to resolve your requirement using two calcs.

• ###### 4. Re: only select End of month date

can you send screen shots of how you did this?

i cant open the attachement

• ###### 5. Re: only select End of month date

Hi Imran,

These are the two calculations.  The first screenshot showed the layout for the calculations:

• ###### 6. Re: only select End of month date

See attached for an example

• ###### 7. Re: only select End of month date

Here is Don's saved as 10.5

• ###### 8. Re: only select End of month date

So Don and I essentially do the same thing to get to the last day of the month.

I use datetrunc to chop any date down to the first of the month.  Then I use dateadd to add one month.  So April 15 truncates to April 1, then add a month goes to May 1.  Then I subtract a day.  April 30.

Tableau date math is great.  This works worthier the month ends on 28 or 30 or 31 (and even gets leap years right.)

I added another calc to evaluate the day of week that the last day is.  If Sunday, subtract 2 days.  If Saturday, subtract one day.  Else keep the last-day-of-month value.

Don just discards Sunday and Saturday, so you don't see anything for June.  I think you want to be able to select for June 29, but maybe not...

Anyway, the components you need are in our two workbooks.

• ###### 9. Re: only select End of month date

PS:  You get stuff on all the date functions here: