Dates introduce a sense of relativity to the absolutes of our data. Whether visualising trends or comparing performance against last year or any specific period, it all centers around the frameworks and reference points provided by dates. Tableau offers 24 distinct Date Time functions, along with an option for configuring Fiscal Dates, making date handling straightforward. This blog aims to explore the mechanics behind frequently employed date functions, followed by an in-depth examination of crafting a Key Performance Indicator (KPI) showcasing 25 commonly utilised date calculations.
Before delving into complex calculations, it's crucial to grasp the fundamentals of widely used Tableau Date functions, each performing task aligned with their names. Here are 9 foundational Tableau functions that serve as building blocks for more intricate calculations:
1. DATEADD
Returns the date the specified interval added to the specified part of that date. For example, adding three months or 1 day to a starting date.
Example: DATEADD (‘day’,1, [New Order Date])
// gives 2/24/2021 when New Order Date is 2/23/2021.2. DATEDIFF
Returns the difference between two dates expressed in specified units.
Example: DATEDIFF ('day', DATE (‘3/25/2021’), DATE (‘3/30/2021’))
// Result = 5Note: The DATE () function used above returns date given an integer, string or other valid date expression. Here it is used to convert valid string to date format.
3. DATENAME
Returns the name of the specified date part as a discrete string.
Example: DATENAME (‘day’, [New Order Date])
// returns "Monday" when the [New Order Date] is ‘1/19/2021’4. DATEPART
Returns specified part of the date of a date as an integer. It works like DATENAME (), the difference being, it is faster as it results in integer than discrete string.
Example: DATEPART (‘month’, [New Order Date], ‘sunday’)
// returns the month number 2 when [New Order Date] is ‘2/23/2021’.Note: The ‘sunday’ above specifies the start of the week argument which suggests our weeks start every Sunday.
5. DATETRUNC
Truncates and returns a date to the beginning of the specified date part.
Example: DATETRUNC (‘week’, [New Order Date],’monday’)
// returns ‘2/8/2021’ (beginning of week- monday) when the [New Order Date] is ‘2/12/2021’
6. ISDATE
Boolean function that returns true if a given string is a valid date.
Example: ISDATE (03/12/2021)
// returns True
7. TODAY () & NOW ()
Date and Datetime functions that return today’s date and Now () returns Current Date and Time.
8. WEEK (MONTH, YEAR, AND QUARTER work similarly)
Returns the week of the given date.
9. ISOWEEKDAY, ISOWEEK
Returns the ISO8601 week-based week of a given date. To know more about the differences between a Standard Gregorian Week and an ISO Week, I would recommend going through this blog
With a grasp of these fundamental Tableau functions, we can proceed to develop the 25 necessary functions to craft dynamic KPIs. These KPIs will enable us to analyse performance details for actuals, vs Last Year (LY), and vs Previous Period (vs PP) across various selected reporting periods:
Selected Duration
Last Week
Year to Date (YTD)
Week to Date
Last N Weeks
Last Month
1. Selected Duration:
For this instance, we would need the user to specify the date range, a starting date and ending date and based on this start and the end points we will filter transactions that fall within this specified duration.
To take user input, we shall make two parameters, Starting Date and Ending Date
1) Order Date b/w keep filter
// Logic: Keep b/w two parameter dates (start and end)
IF [New Order Date]>= [Starting Date] and
[New Order Date] <= [Ending Date] THEN TRUE ELSE FALSE END2) Order Date b/w keep filter (LY)
// Logic: Like the previous calculation just subtract 1 year from both start and end dates
IF [New Order Date] >= DATEADD ('year’, -1, [Starting Date]) and [New Order Date] <= DATEADD ('year’, -1, [Ending Date]) THEN TRUE ELSE FALSE END 3) Order Date b/w keep filter (PP)
// Logic: The ending date becomes the starting date, and it goes backwards same duration (DATEDIFF b/w start and ends) from there.
IF [New Order Date]>= DATEADD ('day’, -DATEDIFF ('day’, [Starting Date], [Ending Date]), [Starting Date]) And [New Order Date] <= [Starting Date] THEN TRUE ELSE FALSE END2. Last Week:
Note: Week Commencing
DATE (DATETRUNC ("week", [New Order Date]))
// gives the week starting date for all dates in the week4) Order Date Keep LW
// Logic: Make the Week Commencing date same as last week’s date (Today () -7)
IF [Week Commencing] = DATETRUNC ('week', TODAY ())-7 THEN TRUE END5) Order Date Keep LW (LY)
// Logic: Make the Week Commencing date same as last week’s date (Today () -7) and subtract 53 weeks from today.
IF [Week Commencing] = DATETRUNC ('week', DATEADD (‘week’, -53, TODAY ()))-7 THEN TRUE END6) Order Date Keep LW (PP)
// Logic: Essentially take off two weeks from today. DATEADD () subtracts one week and then -7 days is taken off.
IF [Week Commencing] = DATETRUNC ('week', DATEADD ('week', -1, TODAY ()))-7 THEN TRUE END3. Week to Date (WTD):
7) Order Date Keep WTD
// Logic: When current week = Order Week
IF [Week Commencing] = DATETRUNC ('week’, TODAY ()) THEN TRUE END8) Order Date Keep WTD (LY)
// Logic: compares the order date to the same week last year, but only up to the current weekday.
IF [Week Commencing] =DATETRUNC ('week’, DATEADD (‘week’, -53, TODAY ())) and ISOWEEKDAY ([New Order Date]) <= ISOWEEKDAY (TODAY ()) THEN TRUE END9) Order Date Keep WTD (PP)
// Logic: if the order date falls within the same week as the previous week. Specifically, it verifies if the order date is on or before the current weekday of the previous week.
IF [Week Commencing] =DATETRUNC ('week', DATEADD ('week’, -1, TODAY ())) and ISOWEEKDAY ([New Order Date]) <=ISOWEEKDAY (DATEADD ('week’, -1, TODAY ())) THEN TRUE END4. Last Month:
10) Order Date Keep Last Month
// Logic: if the order date is earlier than the start of the current month but falls on or after the start of the previous month.
IF [New Order Date] <DATE (DATETRUNC ("month”, TODAY ())) and [New Order Date]>=DATE (DATEADD ("month”, -1, DATETRUNC ("month”, TODAY ()))) THEN TRUE END11) Order Date Keep Last Month (LY)
// Logic: if the order date is earlier than the start of the current month last year but falls on or after the start of the previous month last year
IF [New Order Date] < DATE (DATETRUNC ("month", DATEADD ('year’, -1, TODAY ()))) and [New Order Date]>=DATE (DATEADD ("month”, -1, DATETRUNC ("month", DATEADD ('year’, -1, TODAY ())))) THEN TRUE END12) Order Date Keep Last Month (PP)
// Logic: the order date is before the start of the current month and after the start of the month two months ago
IF [New Order Date] <DATE (DATETRUNC ("month", DATETRUNC ("month", DATEADD ('month', 1, today ())))) and [New Order Date]>=DATE (DATEADD ("month”, -2, DATETRUNC ("month”, TODAY ()))) THEN TRUE END5. Year to Date (YTD):
13) Order Date Keep YTD
// Logic: If the order date is on or after the beginning of the current year and on or before the current date.
IF [New Order Date]>= (DATETRUNC ('year’, TODAY ())) And [New Order Date] <=TODAY () THEN TRUE END14) Order Date Keep YTD (LY)
// Logic: if the order date is on or after the start of the previous year and on or before the current date in the previous year.
IF [New Order Date]>=DATEADD ('year’, -1, (DATETRUNC ('year’, TODAY ()))) And [New Order Date] <=DATEADD ('year’, -1, TODAY ()) THEN TRUE ENDNote: We do not have Previous Period (PP) here because for YTD Previous Period (PP) is the same as Last Year (LY).
6. Last N Weeks:
15) Order Date Keep Last N Weeks
// Logic: the order date is before the start of the current week but falls on or after the start of the week calculated by subtracting the specified number of weeks from the current week
IF [New Order Date] <DATE (DATETRUNC ("week", TODAY ())) And [New Order Date]>=DATE (DATEADD ("week”, - [No of weeks], DATETRUNC ("week", TODAY ()))) THEN TRUE END
The "[No of weeks]" parameter allows users to input an integer indicating how many weeks they wish to look back from the current week.
16) Order Date Keep Last N Weeks (LY)
// Logic: if an order falls within a specified number of weeks before the current week of the previous year by comparing the order date to the start of that week and the week calculated by subtracting the specified number of weeks from it.
IF [New Order Date] <DATE (DATETRUNC ("week", DATEADD (‘week, -53, TODAY ()))) And [New Order Date]>=DATE (DATEADD ("week”, - [No of weeks], DATETRUNC ("week", DATEADD (‘week’, -53, TODAY ())))) THEN TRUE END17) Order Date Keep Last N Weeks (PP)
// Logic: the ending date is calculated first, which becomes the starting date for the specified duration to go backward from there based on No. of Weeks duration.
IF [New Order Date] < DATE (DATETRUNC ("week", DATEADD ('week’, - [No of weeks], TODAY ()))) and [New Order Date]> DATE (DATEADD ("week”, - ([No of weeks]), DATETRUNC ("week", DATEADD ('week’, - [No of weeks], TODAY ())))) THEN TRUE ENDCombining All Calculations for the KPI
To encapsulate the 17 calculations, we can create three Date Filters: Date Filter, Date Filter (LY), and Date Filter (PP). These filters will contain all the calculations and operate based on a Select Reporting Period Parameter, allowing users to specify a period, from the six enlisted above, they want to analyse.
18) Date Filter
CASE [Select Reporting Period]
WHEN "Select Duration" THEN [1) Order Date b/w keep filter]
WHEN "Year to Date" then [13) Order Date Keep YTD]
WHEN "Last Year to Date" THEN [14) Order Date Keep YTD (LY)]
WHEN "Week to Date" THEN [7) Order Date Keep WTD]
WHEN "Last Week" THEN [4) Order Date Keep LW]
WHEN "Last N Weeks" THEN [15) Order Date Keep Last N Weeks]
WHEN "Last Month" THEN [10) Order Date Keep Last Month] END19) Date Filter (LY)
CASE [Select Reporting Period]
WHEN "Select Duration" THEN [2) Order Date b/w keep filter (LY)]
WHEN "Year to Date" then [14) Order Date Keep YTD (LY)]
WHEN "Week to Date" THEN [8) Order Date Keep WTD (LY)]
WHEN "Last Week" THEN [5) Order Date Keep LW (LY)]
WHEN "Last N Weeks" THEN [16) Order Date Keep Last N Weeks (LY)]
WHEN "Last Month" THEN [11) Order Date Keep Last Month (LY)]
END20) Date Filter (PP)
CASE [Select Reporting Period]
WHEN "Select Duration" THEN [3) Order Date b/w keep filter (PP)]
WHEN "Year to Date" then NULL
WHEN "Week to Date" THEN [9) Order Date Keep WTD (PP)]
WHEN "Last Week" THEN [6) Order Date Keep LW (PP)]
WHEN "Last N Weeks" THEN [17) Order Date Keep Last N Weeks (PP)]
WHEN "Last Month" THEN [12) Order Date Keep Last Month (PP)]
ENDThese CASE Calculations function in an interconnected manner, where choosing a Reporting Period determines the appropriate calculation for filtering dates concerning the Actuals, Previous Period, and Last Year.
Additionally, by utilising these three case filter calculations, we can devise aggregation calculations that consolidate measures when these filters yield a True result. This will give us our final five calculation types that would eventually become our KPI actuals and changes.
21) Sales (Actual)
IF [18) Date Filter] =TRUE THEN [Sales] END22) Sales (LY)
IF [19) Date Filter (LY)] =TRUE THEN [Sales] END23) Sales (PP)
IF [20) Date Filter (PP)] =TRUE THEN [Sales] END24) Sales (vs LY)
(SUM ([21) Sales (Actual)])/SUM ([22) Sales (LY)]))-125) Sales (vs PP)
(SUM ([21) Sales (Actual)])/SUM ([23) Sales (PP)]))-1This collection of five calculations can be adjusted and combined as needed to construct key performance indicators (KPIs) for various metrics such as orders, profit, and so forth.
Example: With Today’s Date as Saturday 27th April 2024, we will get following date ranges under different reporting periods based on our calculations and other parameter values:
Starting Date: 26/02/2024
Ending Date: 26/04/2024
No. of Weeks- 4
Reporting Period | Actual | Last Year | Previous Period |
Select Duration | 26/02/2024 – 26/04/2024 | 26/02/2023 – 26/04/2023 | 26/12/2023- 26/02/2023 |
Year to Date | 01/01/2024- 27/04/2024 | 01/01/2023- 27/04/2023 | Same as LY |
Week to Date | Mon, 22/04/2024- Sat- 27/04/2024 (Week 17) | Mon, 18/04/2023- Sat- 23/04/2023 (Week 17) | Mon, 15/04/2024- Sat- 20/04/2024 (Week 16) |
Last Week | Mon, 15/04/2024- Sun- 21/04/2024 (Week 16) | Mon, 10/04/2023- Sun- 16/04/2023 (Week 16) | Mon, 08/04/2024- Sat- 14/04/2024 (Week 15) |
Last Month | 01/03/2024 – 31/03/2024 | 01/03/2023 – 31/03/2023 | 01/02/2024 – 29/02/2024 |
Last N Weeks N=4 | 25/03/2024 (Week 13) – 21/04/2024 (Week 16) | 20/03/2023 (Week 13) – 16/04/2024 (Week 16) | 26/02/2024 (Week 9) – 24/03/2024 (week 12) |
We can now design our dynamic KPI boxes, based on these calculations. Here’s the Tableau Public Dashboard, feel free to download it and reverse engineer the calculations, sheets, or dashboard.
Please Note, the Week Start for this Dashboard is set to Monday and Fiscal Year Starts in January.
You can change these setting under Date Properties for your Data Source, from the menu shown below based on your use case.
However, if your application involves a fiscal year calendar, it would be advantageous to utilise a calendar lookup table or a Date Dimension Table joined to your dataset, as commonly practiced in data warehousing. This approach, as emphasised in a blog by Ken Flerlage, simplifies the process of constructing these calculations on top of this date dimension table.
Lately, I've been employing date dimension tables more frequently, and I plan to delve into how I've utilised them to create key performance indicators (KPIs) in one of my forthcoming blog posts.
Stay Tuned for more posts!!
Signing Off,
Yash
Comments