KNOWLEDGE BASE

Using Date Functions with Fiscal Years


Published: 28 Jun 2013
Last Modified Date: 10 Jan 2020

Issue

When using date functions, such as DATEPART(), then the fiscal year start is ignored.

Environment

Tableau Desktop

Resolution

Create a calculation that returns the expected fiscal date part number to use in future calculations. Depending on the date level needed and the use case, the exact formula will differ. In the below examples, the fiscal year start for [Order Date] is set to February.
CLICK TO EXPAND STEPS
Example 1: Find fiscal year number
  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Fiscal Year Number"
    2. In the formula field, create a calculation similar to the following:
      YEAR( DATEADD('month', 11, [Order Date]) )
    3. Note: the 11 should be replaced with the correct number to shift January to the first month of the fiscal year.
  3. Use [Fiscal Year Number] in any calculation requiring the fiscal year number.
  4. (Optional) Convert the newly created calculated field to a Dimension to use it with the other date field(s) in the view. 
To view the steps showed in the below video, please expand the above section. Note: the video has no sound.
 
CLICK TO EXPAND STEPS
Example 2: Find fiscal quarter number
  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Fiscal Quarter Number"
    2. In the formula field, create a calculation similar to the following:
      DATEPART('quarter', DATEADD('month', -1, [Order Date]))
    3. Note: the -1 should be replaced with the correct number to shift January to the first month of the fiscal year.
  3. Use [Fiscal Quarter Number] in any calculation requiring the fiscal quarter number
  4. (Optional) Convert the newly created calculated field to a Dimension to use it with the other date field(s) in the view. 
To view the steps showed in the below video, please expand the above section. Note: the video has no sound.
 
CLICK TO EXPAND STEPS
Example 3: Find fiscal month number
  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Fiscal Month Number"
    2. In the formula field, create a calculation similar to the following:
      IF MONTH([Order Date]) - 1 > 0
      THEN MONTH([Order Date]) - 1 
      ELSE MONTH([Order Date]) + 12 - 1 
      END
    3. Note, the -1 should be replaced with the correct number to shift January to the first month of the fiscal year. If the fiscal month number if zero or lower, than we need to add 12.
  3. Use [Fiscal Month Number] in any calculation requiring the fiscal month number
  4. (Optional) Convert the newly created calculated field to a Dimension to use it with the other date field(s) in the view. 
To view the steps showed in the below video, please expand the above section. Note: the video has no sound.
 
CLICK TO EXPAND STEPS
Example 4: Find fiscal week number
  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "First fiscal day"
    2. In the formula field, create a calculation similar to the following:
      DATE( "2/1/" + STR( YEAR( DATEADD( 'month', -1, [Order Date] ))))
    3. Note: the -1 should be replaced with the correct number to shift January to the first month of the fiscal year.
  3. Create a calculated field with a name like "Fiscal Week Number" with a calculation similar to the following:
    DATEDIFF('week',[First fiscal day],[Order Date]) +1
    
  4. Use [Fiscal Week Number] in any calculation requiring the fiscal week number.
  5. (Optional) Convert the newly created calculated fields to a Dimensions to use them with the other date field(s) in the view. 
To view the steps showed in the below video, please expand the above section. Note: the video has no sound.
 

Cause

By design, date functions currently return values based on the calendar year.
Did this article resolve the issue?