In some situations, a date field needs to be expressed in terms of an organisation's fiscal year. A standard calendar year runs from 1st January until 31st December, but an organisation's fiscal year might start in a different month (most likely April). In this case, we would need to display fiscal dates (Fiscal Year, Fiscal Quarter and Fiscal Week Number) rather than standard calendar dates. This blog post will run through how we can convert standard calendar dates into fiscal dates.
Let's go through an example:
Step 1: Insert the data source (in this case '2018 Orders') into Tableau Prep. Once the data set is in the view, we can add a clean step to showcase the data in a user friendly way (see below).
Step 2: Looking at the table above, we can see that the 'Date' field is currently a string data type. However, we need to change this to a 'Date' data type - to do this we click the 'Abc' that appears above the name of the field and select 'Date'.
Step 3: Once the 'Date' field has been changed to a 'Date' data type, we duplicate the 'Date' field by clicking the ellipses that appear when we hover over the field name. This creates a new field which we can rename to 'Fiscal year' (see below).
Step 4: To convert the standard calendar dates into fiscal dates, we click the ellipses > convert dates > custom fiscal year and the following window appears:
In this case we want to covert our standard calendar dates to a standard fiscal year (which starts in April) so we select 'Year number' and 'April'. This means that the fiscal year starts in April so the first month of the year will be April rather than January. After making all of those changes, the data should look like the table below:
