DATEPART Returns Week Value of 53 Instead of 1

Issue

When the data source includes a week that spans the end of one year and the beginning of another, for example if Sunday through Thursday occur in 2013 and Friday through Saturday occur in 2014, the DATEPART function for week and year returns a value of 53 instead of 1.

Environment

Tableau Desktop

Resolution

Option 1

In Tableau Desktop 2019.3 and newer versions, you can switch between Gregorian Calendar and ISO-8601 Week-Based Calendar easily without creating calculated fields. For more information, see ISO-8601 Week-Based Calendar.

Option 2

Ensure that you re using Tableau Desktop 2018.2 or a newer version.
Create a calculated field with a formula similar to the following:

datepart('iso-week', [Date])

For more information, see Gregorian Calendar vs. ISO 8601 Standard.

Option 3

Create two calculated fields to use in place of date fields for Week and Year:

Select Analysis > Create Calculated Field to create the calculated field for week.
In the Calculated Field dialog box that opens, type a name for the field. For example, Week Field.
In the Formula box, type the following formula:
If datepart('week',[Date])=53 then 1 else datepart('week',[Date]) end
and then click OK.
Select Analysis > Create Calculated Field to create the calculated field for year.
In the Calculated Field dialog box that opens, type a name for the field. For example, Year Field.
In the Formula box, type the following formula:
If datepart('week',[Date])=53 then datepart('year',[Date])+1 else datepart('year',[Date]) end
and then click OK.
Right-click Week Field in Measures and select Convert to Dimension. Repeat for Year Field.
Use the new calculated fields in the view in place of the Week and Year date fields