Date logic for unusual fiscal year

[Wenchie]

I need to specify in ongoing report to pull data for a fiscal years that run from Nov 1 - Oct 31
Someone in house said to use this - but it's not exactly working - any help would be appreciated!

BeforeReadingRecords;

if month(today) in [11,12] then

date(year(today),11,01)

Else

if not(month(today) in [11,12]) then

date(year(today)-1,11,01)



End Date:

currentdate – 1

OR

Monthend

 

[pmax9999]

Please explain what you are trying to do. Eg, do you want the report to only pull data from the current fiscal year, do you want to the data to the current date, or up to the end of the previous month (if the report is run during November, would you want the report to show just the results for the month to date, or would you expect it to return the full previous fiscal year results).

The logic to do what you need is not going to be difficult, but we need to understand your objective.

Cheers
Pete

 

[Wenchie]

Pete -

I only want it to pull from the current fiscal year (from 1 Nov 2013 onward) and it will run monthly and should stop at the end of each full month. So if I ran it March 3 it would give data from Nov - Feb only?

Thank you so much!
Holly

 

[pmax9999]

Hi Holly

Try something like this:

(
    	(
        	Month(CurrentDate) >= 11 and 
        	{Table.DateField} >= Date(Year(CurrentDate), 11, 1)
    	)   
    	or
	(
        	{Table.DateField} >= Date(Year(CurrentDate)-1, 11, 1)
    	)
)
and
{Table.DateField} <= Maximum(LastFullMonth)

Cheers
Pete

 

[Wenchie]

Thank you so much Pete!