Hi:
I am trying to report on a count of cases for the current fiscal year (FY) to the last day of the previous month. Our FY runs from October 1, 2015.

Last year I created a report and as the start date, used:

if {database.Date} in date(year(currentdate),10,1) to currentdate - Day(CurrentDate) then 1 else 0

This worked in 2015. However, we're now in 2016, and it no longer works. It appears to interpret the above formula as:
From 10/1/2016 to 12/31/2015.

So I tried:

if {Epiccases.Date} in dateserial(2015,10,1) to currentdate - Day(CurrentDate) then 1 else 0

which works perfectly. However, the year 2015 is hardcoded, which is what I'm trying to avoid.

Ideally, the desired solution would be for the formula to automatically count from the most recently passed October 1 to the last day of the previous month (which, as of today is Dec 31, 2015), no matter what year it currently is. So after October 1, 2016, it should start using 10/1/2016 as the beginning date, and so on.

Is this possible? Thank you in advance.

Edited by kitster100 - 01 Jan 2016 at 5:51am

No. I want a count of all cases from October 1, 2015 to December 31, 2015. Then when I run it next month (February 2016) , I want a case count from October 1, 2015 to January 31, 2016. And I want the FROM criteria to reset each October to the most recent October. So when I run it in November 2016, it will capture ONLY cases from October 2016 forward.

Hmm, I don't want that. When I run it in October, then my desired results should be from the PREVIOUS year's October 1 to the last day of the previous month. So, for example, if I run the report on October 5 2016, then the desired result will be all cases from October 1, 2015 to September 30, 2016.

This gives me ALL the results in the database. Thanks much for your efforts. I will continue to play with it. In the meantime I'll just use the hardcoded formulas.
Thanks again.

No, because I have other formulas which require data from the last Fiscal Year (October '14 thru September '15).

My report is a crosstab with the following columns (the date range I'm trying to capture is in italics, in the parentheses):

Prev Month (December 2015) | Same Month Last Year (December 2014) | Current FY YTD (October '15- December '15) | Previous FY YTD (October '14 - December '14).

I have no parameters set and no conditions in the select statement. Instead, I have formulas for each of the result sets above to set the date criteria. My results depend on calculating from the most recent October (for current FY) and for the October before that (for the previous FY). Getting the formula to recognize the October start of our FY has been the problem. So far, all I've been able to come up with is hardcoding in the October 1 start date, as I mentioned in the original post.

Any ideas?
Thank you for your help.