Jim Dehner 

 Tableau Visionary and Ambassador

Tableau How to’s,  Use Cases, and Forums Questions

Hey #DataFam

Let’s Talk

Open for

Post Links

Fiscal Dates

Many businesses use a fiscal calendar for accounting and reporting. Their fiscal year can start on the first day of any month and may not align with the start of a calendar quarter.  Tableau makes it easy to adjust for the fiscal year start.  

Open the date field and set the Default Property – Fiscal Year Start to the proper Month

 

 
 
The fiscal year starts in the month selected (August in this example) and ends 11 months later (July) in the subsequent calendar year 
Quarters are 3 month periods starting with the fiscal year start
 
 
 
 
The date field can be used in any type of chart, can be filtered, or placed on tile cards to adjust to the proper fiscal year
 
 

 

 

But problems arise when doing any calculation based on date functions 

 
In the LOD below the Year(Order Date) is the calendar year – not the anticipated fiscal year 
 
{ FIXED Year([Order Date]):sum([Sales])}
 

 

Date functions don't recognize fiscal years so a different approach is needed 

 

This example came from a recent TCish BrainDate-

 

 

The user has an August fiscal year start and wanted to be able to select any calendar date and display the current and the prior  YTD and QTD sales 

 

 

(Note: in the US retail sector February and August fiscal year starts are not uncommon – Inventory taxes are often evaluated on inventory levels at the end of January  so retailers set the FY start in February or 6 months later – manufacturers who sell into the segment frequently want to be able to see reports or analyses in the retailer Fiscal Year)

 

Conceptually, the approach is simply determining the fiscal year start date based on a parameter and the Current Date (a calendar date) set by another parameter – used as the end date for YTD and QTD analysis.  

 

 

Note: The formulas to determine the quarterly adjustment between the fiscal quarters and the calendar month were difficult and took some trial and error – for that reason, I included them in the text so you could copy and paste them – the other calculations deciding which calendar dates are between the start and end dates of the fiscal period. 

 

Start by creating a parameter to select a start month – The values are 1-12 and the display is the Month to make it easy on the user

 

 

 
 

another parameter is simply a date parameter 

 

 

 

The FY start date is just based on the Start date parameter converted to a real date

 

 

  Dateadd('year',0,[Convert Start date to Real date])

 

 

But finding the month of the quarter start date will take a series of calculations  

 

The basis to determine which Calendar Year contains the Fiscal Quarter start –  if the value is positive the Current Date and the FQ start are in the same calendar year – if the value is negative the FQ starts in the prior calendar year

 

 

month([Current Date]) – Min(Int([FY Start Month Parameter]))

 

The adjustment that will be used in the Dateadd function is actually

 

 

If [Number months Current date and FY Start]>= 0

then [Number months Current date and FY Start] 

ELSE 12+[Number months Current date and FY Start] end

 

The FQ for the Current date is 

 

 

If  [Month adj to FQ1] <=2 then Dateadd('month',0,[Start Date – Real])
ELSEIF   [Month adj to FQ1] <=5 then Dateadd('month',3,[Start Date – Real])
ELSEIF   [Month adj to FQ1] <=8 then Dateadd('month',6,[Start Date – Real])
ELSEIF   [Month adj to FQ1] <=12 then Dateadd('month',9,[Start Date – Real])END

 

For the prior year fiscal year start the formula simply is:

 

 

Dateadd('year',-1,[Start Date – Real])

The prior quarter is 

 

If  [Month adj to FQ1] <=2 then Dateadd('month',-3,[Start Date – Real])
ELSEIF   [Month adj to FQ1] <=5 then Dateadd('month',0,[Start Date – Real])
ELSEIF   [Month adj to FQ1] <=8 then Dateadd('month',3,[Start Date – Real])
ELSEIF   [Month adj to FQ1] <=12 then Dateadd('month',6,[Start Date – Real])
END

 

 

The formulas of the YTD QTD prior YTD and Prior QTD are straightforward LODs that total the sales between the period start and end dates

 

 

YTD sales for the Current Fiscal year

 

 

{ FIXED [Order Date]:(if min([Order Date])>=[Current FY start date] 
and min([Order Date])<=[Current Date] then sum([Sales]) end)}
 

QTD sales in the current FY

 

 
{ FIXED [Order Date]:
If Min([Order Date])>=[Current Q start date] and Min([Order Date])
<=[Current Date] then sum([Sales]) end }

 

Prior fiscal year YTD sales

 

{ FIXED [Order Date]:(if min([Order Date])>=[Prior FY start date] 
and min([Order Date])<=DATEADD('year',-1,[Current Date]) 
then sum([Sales]) end )}

 

Prior year fiscal quarter sales 

 

{ FIXED [Order Date]:if Min([Order Date])>=[Prior Q start date] 
and Min([Order Date])<=DATEADD('month',-3,[Current Date]) then
sum([Sales]) end }
 
and the YoY and QoQ formulas are LOD's 
 
sum({ FIXED :(sum([Current Year YTD sales])-sum([Prior Year YTD sales ]))})/
sum({ FIXED :  sum([Prior Year YTD sales ])})
 
and 
 
sum({ FIXED :(sum([Current Year QTD sales])-sum([ Prior QTD sales]))  })/
sum({ FIXED :sum([ Prior QTD sales])  })

 

 

In table form, the user can select the starting dates and the table updates to return the year and quarter to date values 

 

 

 
 

A look at the monthly detail makes it easier to see what the formula do 

 

The current YTD include the months of August – December 5th, QTD is November  – December 5th

The prior YTD is in FY 17 August 2017 – December  5, 2017, and the prior QTD is in FY 18 August 2018 – September 5, 2018

 

 

 

 

Hope it makes sense to you

The workbook which contains all the complex formulas can be downloaded at Link to Tableau Public

 

Enjoy

Jim

 

Leave a Reply

Your email address will not be published.