Using Date Functions with Fiscal Years

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

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

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.
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

To voice your support for the inclusion of this enhancement in a future product release, add your vote to the following Community Idea: Make Date functions work when setting fiscal year different than January