KNOWLEDGE BASE

DATEPART Returns Week Value of 53 Instead of 1


Published: 08 Jan 2014
Last Modified Date: 06 Dec 2017

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

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

Cause

To allow for more flexibility in how weeks are defined, Tableau Desktop calculates the week number based on the week start day, set at the data source level, within each year.

For some European locales, the date can be formatted to display using the ISO 8601 standard. See Using Week-Based Placeholders in Custom Date Formats for directions.
Did this article resolve the issue?