4 Replies Latest reply on Mar 14, 2017 10:32 AM by Christian Schwehm

# Filtering on last business day of a month

Hello,

I've got a Problem to filter a list of dates in an appropriate way.

How can I filter a list of days, so that I only see the last weekday of a month (i.e. no saturday or sunday). Later on I would like to apply a table calculation that gives me the respective return.

A related question would be to filter the data that gets into the workbook already at the datasource Level. Is there a way to Limit the data at the very beginning? In reality I have a huge amount of daily data, but it would be sufficient to work with the month-end values.

A Tableau 10.1 Workbook is attached and the Excel-file for the Import is attached.

Thanks for your help :-)

• ###### 1. Re: Filtering on last business day of a month

Hi Christian,

I think you can get what you want by creating a few custom dates and a filter.

1.  Create a custom date for days, use date value.

2. Create a custom date for weekdays.

3. Create a custom date for Months, use date part

4.  Create a Calculated Field called Max Day Filter: { FIXED [Date (Months)] : MAX([Date (Days)]) } = [Date (Days)]

5.  You can then edit your data source and Apply the Max Day filter = True and use your custom date for weekdays to filter out Saturday and Sunday

Let me know if you have any questions.

Regards,

Ivan

• ###### 2. Re: Filtering on last business day of a month

This calc will give you the last day of the month that is not a Saturday or Sunday... Let me know if this helps

IF DATENAME('weekday',(DATEADD('day',-1,  MAKEDATE(YEAR([Date]), MONTH([Date])+1,1)))) = 'Saturday' Then (DATEADD('day',-1,  MAKEDATE(YEAR([Date]), MONTH([Date])+1,1)))-1

ELSEIF DATENAME('weekday',(DATEADD('day',-1,  MAKEDATE(YEAR([Date]), MONTH([Date])+1,1)))) = 'Sunday' Then (DATEADD('day',-1,  MAKEDATE(YEAR([Date]), MONTH([Date])+1,1)))-2

Else (DATEADD('day',-1,  MAKEDATE(YEAR([Date]), MONTH([Date])+1,1)))

END

2 of 2 people found this helpful
• ###### 3. Re: Filtering on last business day of a month

Great thanks a lot :-)

• ###### 4. Re: Filtering on last business day of a month

Thanks Ivan for the answer, cool :-)