Published: 28 Jun 2013 Last Modified Date: 03 Jan 2024
Issue
When using 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.
Environment
Tableau Desktop
Resolution
Use DATEADD('month',...) to shift the actual date to the corresponding relative date. For example, if February is the first month in the fiscal year then February should be shifted to next January. See Additional Information section below for further explanation.
CLICK TO EXPAND STEPS
Example 1: Find fiscal year, quarter, or month number
Select Analysis > Create Calculated Field
In the Calculated Field dialog box that opens, do the following, and then click OK:
Name the calculated field. In this example, the calculated field is named "Fiscal Year Number"
In the formula field, create a calculation similar to the following:
Note: Replace 'year' in the calculation above with 'quarter' or 'month' as needed. Replace 11 with the correct offset, see Additional Information for further explanation.
Use [Fiscal Year Number] in any calculation requiring the fiscal year number.
(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 week number
Select Analysis > Create Calculated Field
In the Calculated Field dialog box that opens, do the following, and then click OK:
Name the calculated field. In this example, the calculated field is named "First fiscal day"
In the formula field, create a calculation similar to the following:
Use [Fiscal Week Number] in any calculation requiring the fiscal week number.
(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
The date functions currently return values based on the calendar year by design.
Additional Information
What date shift to use?
The standard definition of fiscal year uses the calendar year number of the last month in 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
February
11
March
10
April
9
May
8
June
7
July
6
August
5
September
4
October
3
November
2
December
1
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.