Crystal reports select dates 6 months to end of last month

I am working on a daily sales report that sets a sales goal for the current month based on the total sales of the past six months.  So for this month (August) I need to see the total sales from 2/1/2015 through 7/31/2015 to calculate the new goal for the month.  I need that range to change as the months go by,

Right now my formula looks like this month(currentdate)-6 would give me the start of the range by returning  a 2 for February.  However, if the current month was March the same formula returns -3 instead of 10 for October (previous year).

I'm also not sure how to get the end of the range (last day of previous month).
-Darvin-Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
you need to account for end of year, best option is to actually use a date function to subtract six months from the date versus converting the data to a numeric and then subtracting 6 where you do not know.
If you wish to continue with what you have, your best bet is to test the results and if it is negative, subtract one from year while adding 12 to the months value.
a result of in March/2015  is -3, 2015, the end result after the adjustment will be 9,2014. (-3+12,2015-1)

There are several examples of date handling in crystal datediff (if available in your version) or using a calculation test to adjust/mimic the behavior.

Not sure what you mean by end date of the last month.  You want to go from begining of month six months ago till the end of the month preceding the one for which the report is being done?

you know the current month, you want the data from Feb 1st through July  31st?
-Darvin-Author Commented:
Correct, I need the report to consider sales from Day 1 of the start of the range to the last day of the end of the range.  I am accomplishing this now with a date range prompt but I need to automate it so the report can run without user input on selecting the date range.  In this case it's Feb 1 through July 31st.  August isn't considered a part of the goal set until September 1.

What is the formula that I can put into the report selection criteria to accomplish this?
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
{Sale.Date} in date(dateadd('m',-5,minimum(lastfullmonth))) to maximum(lastfullmonth)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-Darvin-Author Commented:
PERFECT!  Exactly what I was looking for! :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.