Fiscal/financial year date calculations

As a followup to my previous article on calculation of dynamic dates I will demonstrate how we can make similar calculations that take the company's financial year into consideration.

This is important for companies that have a financial year that is not coinciding with the calendar year. In Denmark the financial year typically starts at the beginning of a quarter, i.e.:

• 1st of January
• 1st of April
• 1st of July, or
• 1st of October

Thus, if in our dashboard we want to use a "year-to-date" filter then we actually want the filter to span from the beginning of the financial year till today's date.

Such a filter condition can be implemented using this SQL:

DATEADD(dd, 0, DATEADD(mm, 6, DATEADD(yy, CASE WHEN MONTH(getdate()) >= 7 THEN YEAR(getdate()) ELSE YEAR(getdate()) - 1 END - 1900, 0)))

The result of this expression (assuming today's date is January 13th 2012) is:

'2011-07-01'

i.e. an ISO formatted date, which should be valid in any SQL Server.

The above example assumes that the financial year starts on July 1st, but obviously you can use the approach for any start day of the financial year.

To change the starting month of the financial year you simple have to modify the script in two places:

• 6: Replace this value with the last month in the financial year
• 7: Replace this value with the first month in the financial year

Filter modifiers
As usual, date functions are very useful with filter modifiers. Using a filter modifier you can implement a report where the user selects a date and then your filter modifier transforms the selection into a "year-to-date" date range using the user's selection.

To use the above script in a filter modifier you simply have to replace the occurences of "getdate()" with the desired filter modifier placeholder, {Value1} or {Value2}.

I.e. you need to use this variation of the SQL expression:

DATEADD(dd, 0, DATEADD(mm, 6, DATEADD(yy, CASE WHEN MONTH({Value1}) >= 7 THEN YEAR({Value1}) ELSE YEAR({Value1}) - 1 END - 1900, 0)))