How to… set Custom Fiscal Years

February, April or October? When does you financial year start? Probably something different to these options but lots of your reporting will need to reference these fiscal years in a lot of your analytics. Maybe you want Academic Years to start in August or September? For a long time, custom dates have been formed through lengthy calculations in both Prep Builder and Tableau Desktop. With the release of Prep Builder 2020.3.3, the development team have given us easier access to use Fiscal Years (the name of the functionality but Academic Year changes would work the same too).

What do we mean by Fiscal Year?

In the United Kingdom, many people and organisations’ fiscal year starts in April as that is when the tax year starts over again. This means whether you are individual or organisation recording your earnings for financial recording, you start from zero in April. This means that if we were to conduct financial analysis, determining which date sits in which financial year used to take a fair few calculations for everyone.

Let’s go through an example. Maybe my financial year starts in September as that was when my first started trading and no-one has ever changed it since. Well in that case, I’d need to set my Financial Year to September. Today’s date is 16th September 2020. This means that today is actually in Financial Year 2021 as the year will finish in 2021. However, if I extract the year from today’s date, I’d get 2020. Also, on a normal calendar basis, September would be in the third quarter of the year (also known as Q3) but for my fiscal year reporting I’d want today to be in the first quarter (Q1).

Prep’s Fiscal Date functionality

When Prep introduced the ability to convert dates into it’s separate date parts in just a couple of clicks, it helped a lot of people avoid having to write date calculations. The Convert Date menu has a new addition - Custom Fiscal Year. When using a field that is a date data type, the Convert Date menu can be found in the ellipsis menu in the field in a Clean step.

When you enter the field, at the bottom of that list is Custom Financial Year. Selecting this option brings up a new dialogue box to configure what part of the Fiscal Year you require and which month your fiscal year should start from.

The date parts available are:

• Year Number (ie 2020, 2021)

• Quarter Number (1, 2, 3 or 4)

• Month Number (1,2… 12)

• Week Number (1, 2… 52, 53)

After you have selected the date part you want, you can then select the starting month of your fiscal year.

Normally the Fiscal Year converts the original field into the date part requested. To demonstrate the change I have duplicated the date field. In the screen shot below, I have also renamed the fields for clarity. The output of the Custom Fiscal Year is in the field called Fiscal Year. The image demonstrates by setting the April as the start of the Fiscal Year, 1st April 2020 (UK date format) turns into Fiscal Year 2021.

When duplicating the input date again and selecting Quarter, 1st March 2020 converts to the 4th Quarter.

Summary

This functionality will speed up working with dates and especially adding fiscal year date parts to make your analytical process much faster. If you need the original date in the output data set, you will need to duplicate the date before applying the Custom Fiscal Date. The Preppin’ Data team are likely to have a lot of fun with this functionality going forwards so we will have examples to practice on soon.