KNOWLEDGE BASE

DATEPART Returns Week Value of 53 Instead of 1


Published: 08 Jan 2014
Last Modified Date: 02 Aug 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

Tableau Desktop interprets 12/31/2012 as week 53 of 2012 instead of week 1 for 2013 per the ISO 8601 standard. This behavior is currently by design as Tableau Desktop does not explicitly support the ISO 8601 standard for date interpretation. 
Did this article resolve the issue?