I am currently working on custom SSRS dashboards performance tuning and related exercises. One task among all is to automate the Fiscal Year Start Date and Fiscal Year End Date based on which Fiscal Year we are in.

If the fiscal year is the same as calendar year, we can hard code the values to 1-Jan-[current year] and 31-Dec-[current year]. Since it’s not in my case, I had to dynamically get the dates from somewhere.

The simplest way for me is to query this from GP Fiscal Periods Setup table, which is SY40101.

Following is the query, if anyone would like to know how the dates are retrieved:

SELECT FSTFSCDY Fiscal_Start_Date,

LSTFSCDY Fiscal_End_Date

FROM SY40101 

WHERE YEAR1 = CASE WHEN MONTH(GETDATE()) >= [first month of your company fiscal year] THEN YEAR(GETDATE()) ELSE YEAR(GETDATE()) – 1 END

With above, I can now be rest assured that by the time a new fiscal year is started, my dashboards would automatically get refreshed with new start & end dates.

This query would also work if the fiscal year is as good as the calendar year.

VAIDY