KNOWLEDGE BASE

How to Switch Between Calendar and Fiscal Year


Published: 20 Feb 2018
Last Modified Date: 05 Oct 2018

Question

How to allow users to switch between Calendar and Fiscal year while retaining correct sort order

Environment

  • Tableau Desktop

Answer

The workbook attached to this article demonstrates the below solution using sample Superstore data set, assuming the fiscal year starts in July.

Step 1 - Create a Parameter

  1. Right-click anywhere in the Data pane of Tableau Desktop and select Create Parameter...
  2. Fill in the different fields as described below: 
    1. Name: Calendar Type
    2. Data type: String
    3. Allowable values: List
    4. Manually add the following values: Calendar, Fiscal

Step 2 - Create three calculated fields

  1. Select Analysis > Create Calculated Field...
  2. Name the calculated field: Display Month
  3. Enter the following formula and click OK
    DATENAME('month', [Order Date])
  4. Select Analysis > Create Calculated Field...
  5. Name the calculated field: Display Year
  6. Enter the following formula and click OK
    CASE [Calendar Type]
      WHEN "Calendar" THEN DATENAME('year', [Order Date])
      WHEN "Fiscal" THEN "FY" + DATENAME('year', DATEADD('month', 6, [Order Date]))
    END
  7. Select Analysis > Create Calculated Field...
  8. Name the calculated field: Month Sort
  9. Enter the following formula and click OK
    CASE [Calendar Type]
      WHEN "Calendar" THEN DATEPART('month', [Order Date])
      WHEN "Fiscal" THEN DATEPART('month', DATEADD('month', -6, [Order Date]))
    END

Step 3 - Build the view

  1. Drag [Display Year] and [Display Month] on Columns
  2. Drag Sales to Rows.
  3. Right-click on [Display Month] and select Sort...
    1. Choose "By Field" and select [Month Sort]
    2. Choose "Minimum" aggregation
    3. Then click OK
Did this article resolve the issue?