Loading

Using Date Functions with Fiscal Years in Tableau

Publiseringsdato: May 1, 2025
Beskrivelse

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

For example, if the fiscal year is set to February, then DATEPART('month', #2/1/2022#) will return 2 even though February should be the first month.

Cause

The date functions currently return values based on the calendar year by design.

Løsning

Use DATEADD('month',...) to shift the actual date to the corresponding relative date. For example, if February is the first month of the fiscal year, then February should be shifted to next January. Follow along in the sample packaged workbook found in the Attachments section of this article and see the Additional Resources section below for further explanation and notes.

Example 1: Find fiscal year, quarter, or 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 Year Number"
    2. In the formula field, create a calculation similar to the following: 
      DATEPART('year', DATEADD('month', 11, [Order Date]) )
    3. Note: Replace 'year' in the calculation above with 'quarter' or 'month' as needed. Replace 11 with the correct offset. See Additional Resources for further explanation.
  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. 


The following video demonstrates the steps listed above. Note: the video has no sound.

 

Example 2: 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', 11, [Order Date] ))))
    3. Note: Replace 11 with the correct offset and "2/1/" with the correct first day of the fiscal year. see Additional Information for further explanation.
  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. 


The video demonstrates the steps listed in Example 2.  Note: the video has no sound.

 

Flere ressurser

What date shift to use?

The standard definition of a fiscal year uses the calendar year number for the last month of the fiscal year. A fiscal year from Feb 1, 2022 to Jan 31, 2023 would be referred to as FY 2023. For this standard, dates should be shifted to their corresponding relative dates in the next calendar year.

For example, the date #2/1/2022# should be shifted to #1/1/2023#. This will allow DATETRUNC('month', DATEADD('month', 11, [Original Date Field] ) ) to return the correct month number (1) and the correct year number (2023).
 

Fiscal Year Starting Month# of months to add
February11
March10
April9
May8
June7
July6
August5
September4
October3
November2
December1


Notes

  • DATETRUNC('<date part>', DATEADD('month', N, [Date Field] )) can be useful in other calculated fields but be careful when exposing this to end users as it may be confusing. It return the correct date grouping, but the exact date will not match the calendar date. For example DATETRUNC('quarter', DATEADD('month', 11, [Order Date] )) = #1/1/2023# will correctly return Feb 2022, Mar 2022, and April 2022, but the end user may be confused by #1/1/2023#.
  • Fiscal week needs a different calculation because just shifting the month to the corresponding relative month next year will cause the weekdays to be grouped by the weeks in the month shifted to. 
  • This solution uses standard calendar date groupings and gives them new fiscal names. Some fiscal year systems use different date groupings. See Create a 4-4-5 calendar with ISO-8601 date functions or Set the Fiscal Year as the Date in the Middle of the Month for other solutions 


Voice your support for the inclusion of the enhancement to Make Date functions work when setting fiscal year different than January in the Salesforce Idea Exchange.
 

Knowledge-artikkelnummer

001473696

Vedlegg

fiscal date parts.twbx

1216 KB

 
Laster
Salesforce Help | Article