Assume you have a series of dated transactions. Crystal allows you to use custom functions for things like Month to Date, Year to Date, etc. However these are always based on calendar periods. Month to Date is Calendar month. Year to Date is based on a calendar year, etc. Here is a method that you can use if your business year is a non-calendar or fiscal year.

1) The first step in doing a Fiscal YTD calculation is to write a formula that calculates the fiscal year of each transaction. There are 2 simple methods that should cover 99.9% of fiscal years. Below I will use the Federal Government fiscal year starts on 10/1 of each year.

Example A. This works only if your Fiscal year starts on the first day of the month. Change the number 10 to be the month in which your Fiscal Year starts. For example the typical non-profit year which starts on 7/1 would use 7.

if Month ( {Transaction.Date} ) >= 10
then Year ( {Transaction.Date} ) + 1
else Year ( {Transaction.Date} )

Example B. This works if your fiscal year starts on any day on or after March 1. Replace the number 92 with the number of days between your fiscal year start date and the first of the following year. For example the typical non-profit year which starts on 7/1 would use 184.

Year ( {Transaction.Date} + 92 )

2) Once you have written this formula the second step is to specify how the end of your YTD period will be determined. It could be based on today’s date, which is “CurrentDate” in Crystal syntax. Or, if you need some flexibility you can prompt the user for a date with a parameter. For this example we will assume today’s date.

3) Now you calculate the current fiscal year using the value from step 2 and using the same method you chose in step 1. It would look something like this:

if Month ( CurrentDate ) >= 10
then Year ( CurrentDate ) + 1
else Year ( CurrentDate )

4) Then you can combine these two to write a formula that totals charges (or whatever) for the period FYTD. It would say:

if {@Fiscal Year} = {Current Fiscal Year}
and {Transaction.Date} <= CurrentDate then {Transaction.Charges} else 0 A total of this last formula would automatically give you total charges for the Current Fiscal Year. You could create a separate formula for Prior Fiscal year (subtract one) and use that to create another column, and even do comparisons between the two. If you need help implementing this in a specific report give me a call (I can be rented). And if you like to learn techniques like this you should check out my library of “Expert Techniques” Guides.