How to Measure Fiscal Year in Tableau

There are a couple of scenarios in the Business Intelligence solution, Tableau, where you may need to create a way to measure the Fiscal Year MTD, QTD, and YTD. An example is laid out below where you can build one chart that will show you MTD, QTD, YTD in the Fiscal Year, as of today.

1. Create a date for Fiscal Year

2. Change Fiscal Year Start month

3. Create Date – Fiscal Year Start Date

4. Change its Fiscal Year Start

5. Create a Sales (MTD) with formula below

6. Create Sales (QTD)

IF [Date – Fiscal Year] < [Date – Fiscal Year Start Date] THENNullELSEIF [Date – Fiscal Year] > (Today()-1) THENNull
ELSEIF DATENAME(‘month’,Today()-1) = ‘July’ OR
DATENAME(‘month’,Today()-1) = ‘August’ OR
DATENAME(‘month’,Today()-1) = ‘September’ THEN
IF DATENAME(‘month’,[Date – Fiscal Year])= ‘July’ THEN
[Sales (CAD)]
ELSEIF DATENAME(‘month’,[Date – Fiscal Year])= ‘August’ THEN
[Sales (CAD)]
ELSEIF DATENAME(‘month’,[Date – Fiscal Year])= ‘September’ THEN
[Sales (CAD)]
ELSE
NULL
END
ELSEIF DATENAME(‘month’,Today()-1) = ‘October’ OR
DATENAME(‘month’,Today()-1) = ‘November’ OR
DATENAME(‘month’,Today()-1) = ‘December’ THEN
IF DATENAME(‘month’,[Date – Fiscal Year])= ‘October’ THEN
[Sales (CAD)]
ELSEIF DATENAME(‘month’,[Date – Fiscal Year])= ‘November’ THEN
[Sales (CAD)]
ELSEIF DATENAME(‘month’,[Date – Fiscal Year])= ‘December’ THEN
[Sales (CAD)]
ELSE
NULL
END
ELSEIF DATENAME(‘month’,Today()-1) = ‘January’ OR
DATENAME(‘month’,Today()-1) = ‘February’ OR
DATENAME(‘month’,Today()-1) = ‘March’ THEN
IF DATENAME(‘month’,[Date – Fiscal Year])= ‘January’ THEN
[Sales (CAD)]
ELSEIF DATENAME(‘month’,[Date – Fiscal Year])= ‘February’ THEN
[Sales (CAD)]
ELSEIF DATENAME(‘month’,[Date – Fiscal Year])= ‘March’ THEN
[Sales (CAD)]
ELSE
NULL
END
ELSEIF DATENAME(‘month’,Today()-1) = ‘April’ OR
DATENAME(‘month’,Today()-1) = ‘May’ OR
DATENAME(‘month’,Today()-1) = ‘June’ THEN
IF DATENAME(‘month’,[Date – Fiscal Year])= ‘April’ THEN
[Sales (CAD)]
ELSEIF DATENAME(‘month’,[Date – Fiscal Year])= ‘May’ THEN
[Sales (CAD)]
ELSEIF DATENAME(‘month’,[Date – Fiscal Year])= ‘June’ THEN
[Sales (CAD)]
ELSE
NULL
END
ELSE
NULL
END

7. Create Sales (YTD)

I hope these steps were helpful in creating a way to measure the Fiscal Year MTD, QTD, and YTD in Tableau. If you have any questions about Tableau or other Business Intelligence solutions, please feel free to contact us.