YTD for the fiscal year

[Guest]

I have a fiscal year from August to July.
How could I set up a date in my query to show YTD showing only completed
months data for the current and previous fiscal years?
For the current period it should be from Aug 04 to Feb 05, and for the
previous one - from Aug 03 to Feb 04.

Thanks

 

[Guest]

It looks like I found out the top date. It should be
DateSerial(Year(Date()),Month(Date()),0) for the current year and
DateSerial(Year(Date())-1,Month(Date()),0) for the previous one.

How about for the first dates in the Between criteria for these years?

I think it could be enough to have just Aug 04 and Aug 03. Bu, how to
interpret it into formulas?

 

[John Vinson]

I have a fiscal year from August to July.
How could I set up a date in my query to show YTD showing only completed
months data for the current and previous fiscal years?
For the current period it should be from Aug 04 to Feb 05, and for the
previous one - from Aug 03 to Feb 04.

Thanks

I'd suggest just storing the date of the transaction in the table; you
could use a query of

= DateSerial([Enter fiscal year, e.g. 2003 for 2003-2004 FY:], 8, 1) AND < DateSerial(Year(Date()), Month(Date()), 1)

for the current fiscal year, and

= DateSerial([Enter fiscal year, e.g. 2003 for 2003-2004 FY:] - 1, 8, 1) AND < DateSerial(Year(Date()) - 1, Month(Date()), 1)

John W. Vinson[MVP]

 

[Guest]

Thanks a lot, John.
I'm going to use the following:

=DateSerial(Year(Date())-2,8,1) And <DateSerial(Year(Date())-1,Month(Date()),1)

for the previous year
and

=DateSerial(Year(Date())-1,8,1) And <DateSerial(Year(Date()),Month(Date()),1)for the current one.

Can it be correct?

I have a fiscal year from August to July.
How could I set up a date in my query to show YTD showing only completed
months data for the current and previous fiscal years?
For the current period it should be from Aug 04 to Feb 05, and for the
previous one - from Aug 03 to Feb 04.

Thanks

I'd suggest just storing the date of the transaction in the table; you
could use a query of

= DateSerial([Enter fiscal year, e.g. 2003 for 2003-2004 FY:], 8, 1) AND < DateSerial(Year(Date()), Month(Date()), 1)

for the current fiscal year, and

= DateSerial([Enter fiscal year, e.g. 2003 for 2003-2004 FY:] - 1, 8, 1) AND < DateSerial(Year(Date()) - 1, Month(Date()), 1)

John W. Vinson[MVP]

 

[John Vinson]

Thanks a lot, John.
I'm going to use the following:
for the previous year
and
Can it be correct?

Well... that will work for some months of the year, not for others. In
November the current fiscal year started THIS year; your algorithm
would give you the PREVIOUS year.

You may need an IIF() on Month(Date()) to pick the right starting
year.

John W. Vinson[MVP]

 

[Guest]

Thanks a lot again, John.

How about if I use something like this:

= IIf( Month(Date()) < 8 , DateSerial(Year(Date())-1,8,1), DateSerial(Year(Date()),8,1) And <DateSerial(Year(Date()),Month(Date()),1) for the current year
and
= IIf( Month(Date()) < 8 , DateSerial(Year(Date())-2,8,1), DateSerial(Year(Date()),8,1) And <DateSerial(Year(Date())-1,Month(Date()),1) for the previous one?