What is a DATEPART in Tableau?

Ever tried to analyze your sales data to find out which day of the week is most profitable or which quarter consistently outperforms the others? To answer these questions, you need to dissect your date fields and pull out specific components, like the "day," "month," or "year." This is exactly where Tableau's DATEPART function becomes your best friend. In this tutorial, we will get straight into what DATEPART does, walk through practical examples, and clarify how it is different from similar-sounding date functions.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is the DATEPART Function in Tableau?

The DATEPART function does exactly what its name suggests: it "parts" out a specific component from your date field. Think of a date like an address - it has multiple parts: a year, a month, a day, and maybe even an hour, minute, and second. The DATEPART function lets you ask Tableau for just one of these pieces.

The most important thing to remember about DATEPART is its output. Unlike other date functions that might return a brand-new date, DATEPART always returns a single whole number, known as an integer.

• `DATEPART('year', [Order Date])` will return the number of the year, like 2024.
• `DATEPART('month', [Create Date])` will return the number of the month, like 7 for July.
• `DATEPART('weekday', [Ship Date])` will return the day of the week as a number, where Sunday=1, Monday=2, and so on.

DATEPART Syntax

The syntax for the function is straightforward:

DATEPART(date_part, date_expression, [start_of_week])

Let's break down each element:

1. <date_part>: This is where you tell Tableau which part of the date you want. It's a string entered in single quotes. Think of it as the question you're asking, like 'year', 'month', 'weekday', etc.
2. <date_expression>: This is simply the date field you're targeting from your data source, such as [Order Date] or [Transaction Time].
3. [start_of_week]: This part is optional. By default, Tableau considers Sunday as the first day of the week ('sunday'). If your business week starts on Monday, you can specify that here by writing 'monday'.

Here are some of the most common date_part values you'll use:

• 'year': Returns the four-digit year.
• 'quarter': Returns the quarter of the year (1, 2, 3, or 4).
• 'month': Returns the month of the year (1 for January, 12 for December).
• 'day': Returns the day of the month (1-31).
• 'weekday': Returns the numeric day of the week (default Sunday=1, Saturday=7).
• 'dayofyear': Returns the day number within the year (1-366).
• 'week': Returns the week number of the year (1-53).
• 'hour', 'minute', 'second': Used for datetime fields to extract a specific time component.

Now, let’s see an example of this function in action.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Use DATEPART: A Step-by-Step Example

Let’s say we want to create a bar chart to see our total sales for each day of the week. This analysis is perfect for spotting trends, like higher sales on Fridays or a dip mid-week, which can help with staffing and marketing decisions. We'll use Tableau's Sample Superstore dataset for this exercise.

Step 1: Create a Calculated Field for 'Day of Week'

First, we need to create a calculated field that uses DATEPART to pull the weekday number from our [Order Date] field.

1. From the Data pane on the left, click the dropdown arrow and select "Create Calculated Field..."
2. Name your new field something clear, like “Day of Week Number.”
3. In the formula box, type the following:

DATEPART('weekday', [Order Date])

Click "OK." You now have a new field in your Data pane that contains a number for each day of the week (1-7) corresponding to each order.

Step 2: Start Building the View

Drag your new calculated field, “Day of Week Number,” from the Data pane onto the Columns shelf. Then, drag your [Sales] measure onto the Rows shelf.

You’ll notice that Tableau might default to showing "Day of Week Number" as a pill labeled as SUM(Day of Week Number). This is because DATEPART returns a number, and Tableau automatically assumes you want to aggregate it. We don't. We want to use it as a category label.

To fix this, right-click the SUM(Day of Week Number) pill on the Columns shelf, and in the dropdown menu, select Dimension. Tableau will now treat each number (1, 2, 3, etc.) as a separate category, which is what we need.

Step 3: Make it User-Friendly with Labels

You should now see a bar chart, but the x-axis shows numbers 1 through 7, which isn't very intuitive for others reading your report. Let’s change those numbers into names like "Sunday," "Monday," etc.

1. Create another calculated field. Name it “Day Name.”
2. Use a CASE statement to translate our number into a name. Enter this formula:

CASE [Day of Week Number]
WHEN 1 THEN "Sunday"
WHEN 2 THEN "Monday"
WHEN 3 THEN "Tuesday"
WHEN 4 THEN "Wednesday"
WHEN 5 THEN "Thursday"
WHEN 6 THEN "Friday"
WHEN 7 THEN "Saturday"
END

Click "OK."

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: A Better Visualization

Drag the [Day Name] field out of the Data pane and drop it directly on top of the Day of Week Number pill in the Columns toolbar above the bar graph.

Presto! Your chart now clearly displays total sales broken down by the actual day of the week. You probably want to add data labels and headers. This chart is now ready to share insights about your weekly sales cycle.

DATEPART vs. DATETRUNC: Understanding the Crucial Difference

A common point of confusion for Tableau users is the difference between DATEPART and DATETRUNC. They sound similar, but they produce wildly different results and are used for different types of analysis.

The easiest way to remember the difference is:

• `DATEPART` extracts an integer. It pulls out a single piece of information, like an ID number of a particular week or month. Think of this function for grouping data for cyclical or seasonal analysis.
• `DATETRUNC` rounds a date down. It returns a brand-new date that is trimmed or "truncated" to the beginning of the period you specify. Think of this as helping you with trend analysis by looking at information along a continuous timeline. When creating time series with DATETRUNC, your result is displayed along a consistent time axis on a visualization's time horizon with your results clearly delineated in consistent periods. With `DATETRUNC('month', [Date])`, a date of July 28th would return July 1st of the same year in the field's data row.

Let's use an example to make this crystal clear. Say your data record is dated July 28, 2024:

• `DATEPART('month', #07/28/2024#)` returns the integer 7.
• `DATETRUNC('month', #07/28/2024#)` returns the date July 1, 2024.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

When should you use one over the other?

Your analysis goals are quite different. So when would be the best approach?

Use DATEPART for Seasonal or Cyclical Analysis: Because it strips away the context of the year or month, DATEPART is perfect for comparing similar periods against each other. For example:

• You can use DATEPART('month', [Sales Date]) to group all of January's sales together from every year in your dataset. This would allow you to look at every January ever to make sure that a particular product drop or an advertising campaign is working. It allows you to build a powerful seasonal comparison without the influence of overall annual growth.
• By running a DATEPART('hour', [Ticket Date]), the IT Department can identify a spike in tickets around every day between 3 PM and 4 PM to predict resource usage in the department by comparing how many agents are being used on any given shift and predicting a work schedule to create the best service while having the lowest payroll usage. The data from previous shifts will allow you to determine the right amount of staffing you will need to service future needs during those shifts. This works because you are taking ticket counts by numbers of tickets, not by individual tickets along a timeline.

Use DATETRUNC for a Time Series or Trend Analysis: When dealing with linear timelines, you can use it to make an apples-to-apples analysis through each year to measure overall sales of tickets by looking along any line on a spreadsheet or a table to show the overall growth for the product over time.

Because DATETRUNC keeps the date information in proper sequential order, you can perform analysis of data looking for how your KPIs grew over any given period and find trends.

Let's check back on the above example. You can check sales again using `DATETRUNC('week', [Sale Start Time])` to build charts showing weekly gross sales over the past two years, where each data point on your line graph represents the first day of that ticket purchase week on your line chart. You could never do this using DATEPART('Day of Week').

You can analyze sales to find your top sales week among all past weeks by looking for week number 34 or other weekly numbers and trying to find if this particular week number every year is part of a seasonal lift due to a new line in products you released that caused a bump in your sales revenue.

More Powerful Uses for the DATEPART Function in Tableau

Once you feel secure enough, you can make calculations with DATEPART to help you answer questions that go far beyond the standard set as seen below.

1. Build Custom Fiscal Calculations

Your yearly starting point may be your biggest asset when looking toward revenue on the year.

1. Find your quarters. Add this formula: (DATEPART('quarter', [Purchase Date])) alongside a case statement to support it. You can have this calculate the first quarter, for example, getting January to March, where a second quarter might run October through December.

Building Weekend vs. Weekday KPIs or Metric Calculation

• `IIF(DATEPART('weekday', [Sale Date]) == 7 OR DATEPART('weekday', [Sale Date]) == 1, "Weekend", "Weekday")`. As shown before, it is wise to add the day names with a CASE. This is a good strategy by making this CASE. It becomes quick work to figure out a sales comparison for those days and can also determine staffing by looking closer at your busiest weekly workflow.

Final Thoughts

Mastering Tableau's DATEPART function opens up countless opportunities for better data analysis as well as many other great benefits of knowing your numbers, including day of the week sales figures or even just finding the sales peak hours for your call centers. It is critical to know the difference between using DATEPART for a time-based analysis or knowing DATETRUNC to build reports of trends.

https://www.graphed.com/register