Creating Custom Date Ranges and “To Date” Filters
If you find yourself reading this article, it’s likely because a client has asked for more flexibility in one of their timeline views. They want the option to switch between daily, monthly, and quarterly views—the whole shebang. What’s more, they also want bi-weekly, demi-annual, and thirds thrown in, because, well, they can and they’re excited, and they assume this will be fun for you, too.
That’s okay; I’ve already feigned the excitement and done the work for you.
The date parts we will cover are:
Days
Weeks
Two-Weeks
Months
Quarters
Thirds
Half Years
Years
The first thing to note is I created a calculation called “Today” because I’m working with the free but static Superstore data set that Tableau provides which is not updated daily. So when my calculations below reference “Today”, I’m linking to the maximum date available in the dataset and not today itself. Ideally, you would need to use the actual TODAY() function which does reflect today’s date. Below each calculation I’ve placed the actual logic used which you can then copy and paste and that text does have the actual TODAY() function in there.
We’ll require a parameter and an all-in-one calculation. The parameter will be of integer type, and we will double-click on the “Display As” column to assign date part names to each integer. Not many people realize you can do this and it makes the creation of multiple calculations much simpler, as they won’t be affected if / when you update any aliases as they will be tied to the number, not the alias.
Using this parameter, we then create a calculation for each date part.
Logic for “Date Parts”:
DATE(CASE [@DateParts]
//days
WHEN 1 THEN [Order Date]
//weeks
WHEN 2 THEN DATETRUNC('week',[Order Date])
//two weeks
WHEN 3 THEN
IF DATEPART('week',[Order Date]) % 2 = 0 THEN DATETRUNC('week',[Order Date])
ELSEIF DATEPART('week',[Order Date]) % 2 = 1 THEN DATETRUNC('week',DATEADD('week',-1,[Order Date]))
END
//months
WHEN 4 THEN DATETRUNC('month',[Order Date])
//quarters
WHEN 5 THEN DATETRUNC('quarter',[Order Date])
//thirds
WHEN 6 THEN
DATETRUNC('month',IF DATEPART('month',[Order Date]) % 4 = 0 THEN DATETRUNC('month',DATEADD('month',-3,[Order Date]))
ELSEIF DATEPART('month',[Order Date]) % 4 = 1 THEN DATETRUNC('month',[Order Date])
ELSEIF DATEPART('month',[Order Date]) % 4 = 2 THEN DATETRUNC('month',DATEADD('month',-1,[Order Date]))
ELSEIF DATEPART('month',[Order Date]) % 4 = 3 THEN DATETRUNC('month',DATEADD('month',-2,[Order Date]))
END)
//half years
WHEN 7 THEN
IF MONTH([Order Date]) <= 6 THEN MAKEDATE(YEAR([Order Date]),1,1)
ELSEIF MONTH([Order Date]) > 6 THEN MAKEDATE(YEAR([Order Date]),7,1)
END
//years
WHEN 8 THEN DATETRUNC('year',[Order Date])
END)
You may have noticed I use a % in the "Date Parts" calculation. This actually represents the ‘modulo’ function, which divides two numbers and gives you the remainder.
For example, under our “two weeks” section, it checks to see whether the week number is evenly divisible by 2 or not. If it is, it will return 0 because there is nothing left over from 2/2, in which case we can DATETRUNC() to the date in question. If it is not perfectly divisible by 2, it will leave a remainder of 1, because odd numbers cannot be evenly divided by 2 and so 1 will always remain.
Likewise, when it comes to the “thirds” section where we are grouping months into fours, we can use DATEPART(‘month’,[Order Date]) % 4 to decipher how many months we need to subtract from the date in order to create buckets that equate to starting dates of January, May, and September.
For example, if DATEPART(‘month’,[Order Date]) % 4 = 0, this means the month is either April, August, or December because 4, 8 and 12 are evenly divided by 4 and therefore leave nothing left over. So we know when the month divided by 4 = 0, we would need to subtract 3 months in order to bucket it with the relevant month of January, May or September.
Likewise, if DATEPART(‘month’,[Order Date]) % 4 = 1, this means that the months are either January, May, or September because 1 % 4 = 1, as 1 cannot be evenly divided by 4, leaving us with the 1 we started with. Likewise, 9 % 4 = 1 because 9 was divided by 4 twice, giving us 8 and leaving 1 left over. So when the result is 1 we know not to subtract any months at all because the months already fall where we want them.
We then wrapped these particular outcomes in a DATETRUNC() function so that the resulting months would always start on the 1st.
Modulo makes writing code so much cleaner because your alternative would be to manually check the month of each date and write 12 different conditional statements.
Usually, clients don’t stop at timeline views and want the same custom dates reflected in their KPIs as well.
That is what this next calculation is for. It is an all-in-one “To Date” calculation that filters results from the start of the latest selected date part period up to the present day.
Logic for “To Date”:
CASE [@DateParts]
//days
WHEN 1 THEN [Order Date] = TODAY()
//weeks
WHEN 2 THEN [Order Date] >= DATETRUNC('week',TODAY()) AND [Order Date] <= TODAY()
//two weeks
WHEN 3 THEN [Order Date] >= DATETRUNC('week',DATEADD('week',-1,TODAY())) AND [Order Date] <= TODAY()
//months
WHEN 4 THEN [Order Date] >= DATETRUNC('month',TODAY()) AND [Order Date] <= TODAY()
//quarters
WHEN 5 THEN [Order Date] >= DATETRUNC('quarter',TODAY()) AND [Order Date] <= TODAY()
//thirds
WHEN 6 THEN [Order Date] >= DATETRUNC('month',IF DATEPART('month',TODAY()) % 4 = 0
THEN DATETRUNC('month',DATEADD('month',-3,TODAY()))
ELSEIF DATEPART('month',TODAY()) % 4 = 1 THEN DATETRUNC('month',TODAY())
ELSEIF DATEPART('month',TODAY()) % 4 = 2 THEN DATETRUNC('month',DATEADD('month',-1,TODAY()))
ELSEIF DATEPART('month',TODAY()) % 4 = 3 THEN DATETRUNC('month',DATEADD('month',-2,TODAY()))
END) AND [Order Date] <= TODAY()
//half years
WHEN 7 THEN [Order Date] >=
IF DATEPART('month',TODAY()) <= 6 THEN MAKEDATE(YEAR(TODAY()),1,1)
ELSEIF DATEPART('month',TODAY()) > 6 THEN MAKEDATE(YEAR(TODAY()),7,1)
END
AND [Order Date] <= TODAY()
//years
WHEN 8 THEN [Order Date] >= MAKEDATE(YEAR(TODAY()),1,1) AND [Order Date] <= TODAY()
END
The “To Date” calculation results in a Boolean field and, depending on the use case, can be used as a filter set to TRUE.
When you’re dealing with custom date ranges, formatting the dates to adapt accordingly is pretty much impossible. As such, we need to create our own strings that adapt to the different names we require. You can simply reflect the starting date for each custom date part range, but I’ve gone a step further and reflected both the start and end dates for each one.
Logic for “Date Parts (String)”:
CASE [@DateParts]
//days
WHEN 1 THEN DATENAME('day',[Date Parts]) + " " + LEFT(DATENAME('month',[Date Parts]),3) + " "
+ DATENAME('year',[Date Parts])
//weeks
WHEN 2 THEN DATENAME('day',[Date Parts]) + " " + LEFT(DATENAME('month',[Date Parts]),3) + " "
+ DATENAME('year',[Date Parts]) + " - " + DATENAME('day',DATEADD('day',6,[Date Parts])) + " "
+ LEFT(DATENAME('month',DATEADD('day',6,[Date Parts])),3) + " "
+ DATENAME('year',DATEADD('day',6,[Date Parts]))
//two weeks
WHEN 3 THEN DATENAME('day',[Date Parts]) + " " + LEFT(DATENAME('month',[Date Parts]),3) + " "
+ DATENAME('year',[Date Parts]) + " - " + DATENAME('day',DATEADD('day',13,[Date Parts])) + " "
+ LEFT(DATENAME('month',DATEADD('day',13,[Date Parts])),3) + " "
+ DATENAME('year',DATEADD('day',13,[Date Parts]))
//months
WHEN 4 THEN DATENAME('day',[Date Parts]) + " " + LEFT(DATENAME('month',[Date Parts]),3) + " "
+ DATENAME('year',[Date Parts]) + " - "
+ DATENAME('day',DATEADD('day',-1,DATEADD('month',1,[Date Parts]))) + " "
+ LEFT(DATENAME('month',DATEADD('day',-1,DATEADD('month',1,[Date Parts]))),3) + " "
+ DATENAME('year',DATEADD('day',-1,DATEADD('month',1,[Date Parts])))
//quarters
WHEN 5 THEN DATENAME('day',[Date Parts]) + " " + LEFT(DATENAME('month',[Date Parts]),3) + " "
+ DATENAME('year',[Date Parts]) + " - "
+ DATENAME('day',DATEADD('day',-1,DATEADD('month',3,[Date Parts]))) + " "
+ LEFT(DATENAME('month',DATEADD('day',-1,DATEADD('month',3,[Date Parts]))),3) + " "
+ DATENAME('year',DATEADD('day',-1,DATEADD('month',3,[Date Parts])))
//thirds
WHEN 6 THEN DATENAME('day',[Date Parts]) + " " + LEFT(DATENAME('month',[Date Parts]),3) + " "
+ DATENAME('year',[Date Parts]) + " - "
+ DATENAME('day',DATEADD('day',-1,DATEADD('month',4,[Date Parts]))) + " "
+ LEFT(DATENAME('month',DATEADD('day',-1,DATEADD('month',4,[Date Parts]))),3) + " "
+ DATENAME('year',DATEADD('day',-1,DATEADD('month',4,[Date Parts])))
//half years
WHEN 7 THEN DATENAME('day',[Date Parts]) + " " + LEFT(DATENAME('month',[Date Parts]),3) + " "
+ DATENAME('year',[Date Parts]) + " - "
+ DATENAME('day',DATEADD('day',-1,DATEADD('month',6,[Date Parts]))) + " "
+ LEFT(DATENAME('month',DATEADD('day',-1,DATEADD('month',6,[Date Parts]))),3) + " "
+ DATENAME('year',DATEADD('day',-1,DATEADD('month',6,[Date Parts])))
//years
WHEN 8 THEN DATENAME('day',[Date Parts]) + " " + LEFT(DATENAME('month',[Date Parts]),3) + " "
+ DATENAME('year',[Date Parts]) + " - "
+ DATENAME('day',DATEADD('day',-1,DATEADD('year',1,[Date Parts]))) + " "
+ LEFT(DATENAME('month',DATEADD('day',-1,DATEADD('year',1,[Date Parts]))),3) + " "
+ DATENAME('year',DATEADD('day',-1,DATEADD('year',1,[Date Parts])))
END
We can do something similar for the “To Date” labels:
Logic for “To Date (String)”:
CASE [@DateParts]
WHEN 1 THEN DATENAME('day',[TODAY()]) + " " + LEFT(DATENAME('month',[TODAY()]),3) + " "
+ DATENAME('year',[TODAY()])
WHEN 2 THEN
DATENAME('day',DATETRUNC('week',[TODAY()])) + " "
+ LEFT(DATENAME('month',DATETRUNC('week',[TODAY()])),3) + " "
+ DATENAME('year',DATETRUNC('week',[TODAY()])) + " - " + DATENAME('day',[TODAY()]) + " "
+ LEFT(DATENAME('month',[TODAY()]),3) + " "
+ DATENAME('year',[TODAY()])
WHEN 3 THEN
DATENAME('day',DATETRUNC('week',DATEADD('week',-1,[TODAY()]))) + " "
+ LEFT(DATENAME('month',DATETRUNC('week',DATEADD('week',-1,[TODAY()]))),3) + " "
+ DATENAME('year',DATETRUNC('week',DATEADD('week',-1,[TODAY()]))) + " - "
+ DATENAME('day',[TODAY()]) + " " + LEFT(DATENAME('month',[TODAY()]),3) + " "
+ DATENAME('year',[TODAY()])
WHEN 4 THEN
DATENAME('day',DATETRUNC('month',[TODAY()])) + " "
+ LEFT(DATENAME('month',DATETRUNC('month',[TODAY()])),3) + " "
+ DATENAME('year',DATETRUNC('month',[TODAY()])) + " - " + DATENAME('day',[TODAY()]) + " "
+ LEFT(DATENAME('month',[TODAY()]),3) + " " + DATENAME('year',[TODAY()])
WHEN 5 THEN
DATENAME('day',DATETRUNC('quarter',[TODAY()])) + " "
+ LEFT(DATENAME('month',DATETRUNC('quarter',[TODAY()])),3) + " "
+ DATENAME('year',DATETRUNC('quarter',[TODAY()])) + " - "
+ DATENAME('day',[TODAY()]) + " " + LEFT(DATENAME('month',[TODAY()]),3) + " "
+ DATENAME('year',[TODAY()])
WHEN 6 THEN
DATENAME('day',DATETRUNC('month',IF DATEPART('month',[TODAY()]) % 4 = 0
THEN DATETRUNC('month',DATEADD('month',-3,[TODAY()]))
ELSEIF DATEPART('month',[TODAY()]) % 4 = 1 THEN DATETRUNC('month',[TODAY()])
ELSEIF DATEPART('month',[TODAY()]) % 4 = 2 THEN DATETRUNC('month',DATEADD('month',-1,[TODAY()]))
ELSEIF DATEPART('month',[TODAY()]) % 4 = 3 THEN DATETRUNC('month',DATEADD('month',-2,[TODAY()]))
END)) + " " + LEFT(DATENAME('month',DATETRUNC('month',IF DATEPART('month',[TODAY()]) % 4 = 0
THEN DATETRUNC('month',DATEADD('month',-3,[TODAY()]))
ELSEIF DATEPART('month',[TODAY()]) % 4 = 1 THEN DATETRUNC('month',[TODAY()])
ELSEIF DATEPART('month',[TODAY()]) % 4 = 2 THEN DATETRUNC('month',DATEADD('month',-1,[TODAY()]))
ELSEIF DATEPART('month',[TODAY()]) % 4 = 3 THEN DATETRUNC('month',DATEADD('month',-2,[TODAY()]))
END)),3) + " " + DATENAME('year',DATETRUNC('month',IF DATEPART('month',[TODAY()]) % 4 = 0
THEN DATETRUNC('month',DATEADD('month',-3,[TODAY()]))
ELSEIF DATEPART('month',[TODAY()]) % 4 = 1 THEN DATETRUNC('month',[TODAY()])
ELSEIF DATEPART('month',[TODAY()]) % 4 = 2 THEN DATETRUNC('month',DATEADD('month',-1,[TODAY()]))
ELSEIF DATEPART('month',[TODAY()]) % 4 = 3 THEN DATETRUNC('month',DATEADD('month',-2,[TODAY()]))
END)) + " - " +
DATENAME('day',[TODAY()]) + " " + LEFT(DATENAME('month',[TODAY()]),3) + " "
+ DATENAME('year',[TODAY()])
WHEN 7 THEN
DATENAME('day',IF DATEPART('month',[TODAY()]) <= 6 THEN MAKEDATE(YEAR([TODAY()]),1,1)
ELSEIF DATEPART('month',[TODAY()]) > 6 THEN MAKEDATE(YEAR([TODAY()]),7,1)
END) + " " + LEFT(DATENAME('month',IF DATEPART('month',[TODAY()]) <= 6
THEN MAKEDATE(YEAR([TODAY()]),1,1)
ELSEIF DATEPART('month',[TODAY()]) > 6 THEN MAKEDATE(YEAR([TODAY()]),7,1)
END),3) + " " + DATENAME('year',IF DATEPART('month',[TODAY()]) <= 6
THEN MAKEDATE(YEAR([TODAY()]),1,1)
ELSEIF DATEPART('month',[TODAY()]) > 6 THEN MAKEDATE(YEAR([TODAY()]),7,1)
END) + " - " +
DATENAME('day',[TODAY()]) + " " + LEFT(DATENAME('month',[TODAY()]),3) + " "
+ DATENAME('year',[TODAY()])
WHEN 8 THEN
DATENAME('day',MAKEDATE(YEAR([TODAY()]),1,1)) + " "
+ LEFT(DATENAME('month',MAKEDATE(YEAR([TODAY()]),1,1)),3) + " "
+ DATENAME('year',MAKEDATE(YEAR([TODAY()]),1,1)) + " - "
+ DATENAME('day',[TODAY()]) + " " + LEFT(DATENAME('month',[TODAY()]),3) + " "
+ DATENAME('year',[TODAY()])
END
So there you have it; nothing in here was rocket science, but it takes quite a lot of time to build from scratch, so hopefully it helps someone reduce their workload.
If you’d like to work with rolling date parts that are tied to the most recent day, you can view our post on Creating Custom Rolling Date Ranges.
