When working on real life cases with data across time periods, it is important to know that company's do not often work according to the calendar year. For example, in the finance industry most companies work within the Financial Year (1st April to 31st March).
Having the ability to create your own Date Ranges in Tableau to be able to analyse your visualisations is incredibly valuable. Today I am going to show you a quick way to check for Current Financial Year, Previous Financial Year and Other.
Here is the calculation which I will break down and explain what each part does.
The Calculation:
IF YEAR(DATEADD("month",-3,[Sales Date])) = YEAR(DATEADD("month",-3,TODAY()))
THEN "Current"
ELSEIF YEAR(DATEADD("month",-3,[Sales Date])) = YEAR(DATEADD("month",-3,TODAY()))-1
THEN "Previous"
ELSE "Other"
END
Breakdown
IF YEAR(DATEADD("month",-3,[Sales Date])) = YEAR(DATEADD("month",-3,TODAY()))
THEN "Current"
Lets Use August 2023 for our [Sales Date]
IF the Year of August 2023 - 3 months (May 2023) = the Year of September 2023 - 3 months (June 2023)
THEN = ‘Current’
—> They both = 2023 therefore, August 2023 is in the 2023/24 Financial Year
Lets use a different example of March 2023 for [Sales Date]
If the Year of March 2023 - 3 months (December 2022) = the Year of September 2023 - 3 months (June 2023)
—> YEAR([Sales Date]) = 2022 and YEAR(Today()) = 2023
—> March 2023 has not met the current condition so we must keep going until we reach our met condition
Continuted Breakdown
ELSEIF YEAR(DATEADD("month",-3,[Sales Date])) = YEAR(DATEADD("month",-3,TODAY()))-1
THEN "Previous"
ELSE "Other"
END
As March 2023 did not meet the previous part of our conditional statement, let’s carry on
IF the Year of March 2023 - 3 months (December 2022) = the Year of September 2023 -3 months - 1 (June 2022)
—> They both = 2022
—> Therefore, March 2023 is in the 2022/23 Financial Year
Conclusion
Hopefully you found this useful! And remember, this doesn't just work for financial years you can use it for any time of year (-2 months if you wanted to your year to start in March)