4 Replies Latest reply on Aug 25, 2016 1:39 PM by chakradhar kakani

# Custom start and end date on one date slider

Hello Tableau Users,

can anyone please help me in a logic for defining a custom start and end date on a date slider. The use case scenario is the start date = (last sunday of the previous month + 1 day) and the end date = (last sunday of the current month). is there any way to write the logic is was able to write the logic but i was getting only the last monday of the previos month but i was not able to get the last sunday.

can any one help me out please.

Thanks

• ###### 1. Re: Custom start and end date on one date slider

How does this look for the End Date?

DATETRUNC('week', DATETRUNC('month', DATEADD('month', 1, TODAY()))-1, 'Sunday')

We first go to the first day of the next month: DATETRUNC('month', DATEADD('month', 1, TODAY()))

We subtract 1 to get to the last day of this month: DATETRUNC('month', DATEADD('month', 1, TODAY()))-1

That actually represents the last week of the month so, find the date of its first day (Sunday) with: DATETRUNC('week', DATEADD('month', 1, TODAY()))-1,  'Sunday')

Start Date could also be represented following that logic as: DATETRUNC('week', DATETRUNC('month', TODAY())-1, 'Sunday') + 1

• ###### 2. Re: Custom start and end date on one date slider

Hi Sanchez thanks for the reply so will it get all the days in between start date = (last sunday of the previous month + 1 day) and the end date = (last sunday of the current month) or will it just get the two dates.?? because I want range of dates beginning from last sunday of the previous month + 1 day and last sunday of the current month.

Appreciate the help once again.Santiago Sanchez

• ###### 3. Re: Custom start and end date on one date slider

No worries! An example is attached. Each calculated field only marks the extremes of your time range:

[Start Date]: DATETRUNC('week', DATETRUNC('month', TODAY())-1, 'Sunday') + 1

[End Date]: DATETRUNC('week', DATETRUNC('month', DATEADD('month', 1, TODAY()))-1, 'Sunday')

You could combine them in another calculated field to use as a filter that catches anything in between. Assuming the field in your data set is called [Order Date] then:

[In Date Range] = [Order Date] >= [Start Date] AND [Order Date] <= [End Date]

• ###### 4. Re: Custom start and end date on one date slider

You are genius