KNOWLEDGE BASE

Using Date Functions with Fiscal Years


Published: 28 Jun 2013
Last Modified Date: 06 Dec 2017

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.
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
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. 3. Use [Fiscal Month Number] in any calculation requiring the fiscal month number
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 "1st fiscal week #" with a calculation similar to the following:
    DATEPART('week', [First fiscal day])
  4. Create a calculated field with a name like "Fiscal Week Number" with a calculation similar to the following:
    IF [Original Week Number] - ( [1st fiscal week #] - 1 ) > 0
    THEN [Original Week Number]- ( [1st fiscal week #] - 1 
    ELSE [Original Week Number] - ( [1st fiscal week #] - 1 ) + 52
    END
    • To find the correct week number we want to subtract one less than the week number of the first week of the year, so that the first week is 1 and not 0. If the fiscal week number if zero or below we add 52.
  5. Use [Fiscal Week Number] in any calculation requiring the fiscal week number.

Cause

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