Tableau fiscal dates are a regular source of confusion. The different date parts and date functions respond in different ways; sometimes respecting the fiscal year and sometimes not.
As a general rule, expect the date functions in formulas to not respect the fiscal years. Then, if they do, that’s a bonus!
This article describes:
- how to set the fiscal year start using Tableau date properties
- how to use a parameter to set the fiscal year start date; which also makes Tableau date functions work for fiscal years
How to set the Fiscal Year start in Tableau
The first thing to do is correctly set up the fiscal year start month.
The Fiscal Year Start is a data source setting. Right click on the data source and Date Properties contains the Fiscal Year Start setting.
However, only setting the Fiscal year start at the data source level may not be enough.
Each individual date field also has a Fiscal Year Start in the Default Properties. This doesn’t always adopt the date source date properties, so always check the setting on the date field as well.
Date Functions and the Fiscal Year
Tableau date functions ignore the fiscal year. In some circumstances Tableau does use the fiscal year, so it’s not a completely useless setting.
For example, set the fiscal year, then drag a date field to Rows. Drill through the date hierarchy and it will respect the fiscal year. The year setting will show “FY year” instead of just the year.
However, try and use the fiscal year in a calculated field, for example to produce the fiscal month number, and it doesn’t work.
If your fiscal year starts in April (which is when the UK tax year begins), it would be reasonable to expect April to be month 1.
Unfortunately, as fiscal years are ignored in DATEPART calculations, April will report as month 4.
Fortunately, there is a simple workaround, using a parameter to set the fiscal years start and manipulate the date.
Use a parameter for Tableau to respect the Fiscal Year in date functions
This solutions uses a parameter for to select the Fiscal Year Start and a DATEADD function to adjust the date.
A quick warning: as this adjusts the actual date, use only to report the fiscal Weeks, Months, Quarters and Years. Don’t use to show the exact date, as it won’t reflect the actual date of the event.
Create a parameter of an Integer data type, with the Allowable values from a List.
There will be 12 allowable values in the list, 1 for each month.
The first value is 0 displayed as Jan, the second value is -1 displayed as Feb, the third value is -2 displayed as Mar, etc. Finish with the value -11 displayed as Dec.
(I prefer to show abbreviated months, but the actual “Display As” text doesn’t matter for the calculation – but obviously it matters for users!)
Jan is redundant, but include for completeness.
Next, create a calculated field using the fiscal event date and the fiscal start parameter.
The calculated field adjusts the actual event date by a number of months, therefore showing the correct fiscal date part. The parameter value defines the number of months adjustment.
For example, in the calendar, April is month 4. To show this as month 1, representing the fiscal month, means subtracting 3 months. This is what the calculated field and parameter are doing.
For this example I am using the Superstore data source [Order Date] field and adjusting to create a Fiscal Order Date:
DATEADD('month',[Fiscal Year Start],[Order Date])
Now use that field in any fiscal date part calculations. For example, to show the fiscal month number and returning month 1 for April fiscal year start:
MONTH([Order Date Fiscal])
The table below shows the [Order Date], with the Fiscal Year set to start in April at both the data source and field level.
Notice the difference between the date parts, depending on whether the [Order Date] or adjusted [Order Date Fiscal] is used.
For completeness, the [Order Date] is on the table, showing how it picks up the correct fiscal periods when not used in a date function.
If clicking through to alter the parameter, this table doesn’t work well on a mobile device, it’s too wide to view properly. And I used the polygon technique to hide the ABC value in the table.
Calculate the Fiscal Year Start Date
Using the parameter adjusted date for any fiscal date calculations makes standard date calculations simpler. For example, to calculate the start date of the fiscal year becomes a straightforward DATETRUNC calculation.
The DATETRUNC function returns the first date of the chosen period. For a year, it returns 1 Jan of that year. Use this to return the first date of the fiscal year:
DATETRUNC('year',[Order Date Fiscal])
This will return the 1st of the month of your fiscal year, depending on start month selected in the parameter.