If your customers or internal teams work with fiscal calendars that do not align with the Gregorian calendar you have probably noticed that the built-in date filters of Tableau become less useful. In the case of this easy step-by-step tutorial, we are going to be looking at how to create a filter for a rolling or last "X" number of fiscal months with calculated fields and a parameter.
If you're not interested in learning how this method works at the moment, head over to the Calculated Field Library and search for "Rolling X Fiscal Months (All)". You can use that calculated field and keep on rolling.
Create A Fake Static Month
Because of our fiscal calendar, we can't just drag our date pill over to the Filter card and use relative month filtering. Tableau does not know that our months do not align with the Gregorian calendar and many fiscal calendars do not align with a simple ISO-Week calendar either. Because businesses do this to us, we need to create a fake date for our month with a static day. Easily copy the below calculated field with the COPY button.
Month Static Day
MAKEDATE([FISCAL_YEAR],[FISCAL_MONTH],1)The result of the above calculated field...
We have tricked Tableau into thinking that our fiscal months align with neat Gregorian calendar months. Now we have something for a DATEADD to work with, but we need to tell Tableau what our current fiscal month is first.
Note: If you are using a live data connection you may need to create your static day fiscal month with something like DATE(STR([FISCAL_YEAR])+'-'+RIGHT('0'+STR([FISCAL_MONTH]),2)+'-01').
Create A Max Fiscal Month
Let's create a MAX fiscal month from our static day fiscal month. The [Date] below is the date dimension of your data set.
Max Month Static Day
{FIXED : MAX(IIF([Date] <= DATE(TODAY()),[Month Static Day],NULL))}We need this calculated field in order to know how many months we're rolling back from.
Create a Parameter
Create a parameter called "Rolling X" or something similar and set the Data type as "Integer". Optionally, feel free to customize the Allowable values for your use case.
Create A Final Calculated Field
This final calculated field ties the first two together and references the parameter we just created.
Rolling X Fiscal Months
[Month Static Day] >= DATEADD('month',-[Rolling X],[Max Month Static Day])AND
[Month Static Day] < [Max Month Static Day]
That is all it takes to create a rolling fiscal month calculated field and filter when your calendar does not match the Gregorian calendar. If you would like to include the current fiscal month simply remove the last line of that calculated field, from the "AND" to the end of the above.
Empower your Tableau development with CopyCalc - the ultimate toolkit designed to supercharge your visualization game. Find useful calculated fields, design with the custom color palette maker, and format messy calculated fields instantly with the formatter.
