Fiscal Period Calculations in Tableau

For the most part it’s fairly easy to calculate fiscal periods in Tableau. Here is one approach that requires two parameters and two date functions. This approach allows us to create custom Fiscal Periods on the fly – which can be useful for building what-if scenarios or for working with data that trickles in over a long period of time, such as rebates or claims, where we may need to exclude a chunk of the most current data because it’s incomplete.

To get started we need to indicate when the Fiscal Period starts and when it ends, hence the two parameters: [CFY Start] and [CFYTD End]. Then we need to determine which data to include in our calculation. The DATEDIFF and DATEADD functions will come in handy for that.

Let’s assume we want to create a Fiscal Year summary for a measure called [Sales] based on the [Order Date]:

FYTD Sales Current =
IF [Order Date] < [CFY Start] THEN Null
ELSEIF [Order Date] > [CFYTD End] THEN Null
ELSE [Sales]
END

To compare the current fiscal year sales with the same period during the previous year, we need to shift everything back by 12 months using the DATEADD function:

FYTD Sales Previous =
IF [Order Date] < DATEADD(‘month’,-12,[CFY Start]) THEN Null
ELSEIF [Order Date] > DATEADD(‘month’,-12,[CFYTD End]) THEN Null
ELSE [Sales]
END

Note: when using the DATEADD function to add or subtract months, we don’t have to worry about leap days – pretty nifty.

A similar approach works for Month to Date calculations. With the DATEDIFF function we get rid of all the data that doesn’t fall into the current month, then we just add up the days we want to include:

FMTD Sales Curent =
IF DATEDIFF(‘month’,[Order Date],[CFYTD End]) <> 0 THEN NULL
ELSEIF [Order Date] > [CFYTD End] THEN NULL
ELSE [Sales]
END

When comparing to the same month a year ago, we can use the same trick of shifting time as we used for our Year to Date Previous calculation:

FMTD Sales Previous =
IF DATEDIFF(‘month’,[CFYTD End],[Order Date]) <> -12 THEN NULL
ELSEIF [Order Date] > DATEADD(‘month’,-12,[CFYTD End]) THEN NULL
ELSE [Sales]
END

Bonus Calculation:

If you need to calculate the number of days in a year, here’s a way that considers Leap Years:

IF (datepart(‘year’,[Order Date]) % 400) = 0 then 366
ELSEIF (datepart(‘year’,[Order Date]) % 100) = 0 then 365
ELSEIF (datepart(‘year’,[Order Date]) % 4) = 0 then 366
ELSE 365
END

The % sign is Tableau’s syntax for modulo calculations.

Enjoy!

Here’s a workbook with examples: