Last Modified Date: 27 Sep 2017
AnswerPrepare a separate database table, or an Excel file/sheet which contains information on Country and corresponding Start Date of Fiscal Year, then join this data with the original data.
Below is an example using SuperStore sample data.
- Create an Excel sheet StartDate that contains information on Country and corresponding Start Date of Fiscal Year.
- Join the StartDate sheet with the "Orders" sheet with join key set to Country.
- Add Sales to Rows shelf.
- Add Order Date to Columns shelf, right-click and choose Month May 2015.
- Add Country to Label.
- Right click on Country and choose Show Filter.
- Create a parameter named FY with the following settings:
- Data Type: String
- Allowable values: List
- List of values: 2012, 2013, 2014
- Right-click on FY and choose Show Parameter Control.
- Create a calculated field named FY Start Date with the following calculation:
- Create a calculated field named Date of FY with the below calculation to filter Order Date:
[FY Start Date]<=[Order Date] and [Order Date]<DATEADD("year", 1 , [FY Start Date])
- Add Date of FY to Filter shelf and choose True.
Note that the Start Dates of different countries vary.
For more information, refer to the sample Excel file and workbook attached.
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Training and Tutorials