Comparing This with Last Year - Rolling month, 3month, Year

I am working on old report created by somebody else. Report is supposed to do rolling month, 3m, Year comparing with a previous Year month, 3m,Year.
I have a question regarding calculating dates and want to understand it better.
Client asked for setup like If run date is 28/03/2019 that rolling month range should be something as:
This year : 28/02/2019 – 28/03/2019.
And
Last Year: 28/02/2018 – 28/03/2018.
Similar patern for month and Year.

What is now in report for calculating This Year Month and Previous Year month is here:
//This Year Month
NumberVar Amount;
Amount:=0;

If Year({@InvoiceDate}) = Year({?Month Of Report}) And  Month({@InvoiceDate}) = Month({?Month Of Report}) then
Amount:= {Invoice.Amount}
Else
Amount:=0;
Amount;

//Last year month

NumberVar LastYearMonthAmount;

If Year({@InvoiceDate}) = Year({?Month Of Report})-1 And  Month({@InvoiceDate}) = Month({?Month Of Report}) then
LastYearMonthAmount:= {Invoice.Amount}
Else
LastYearMonthAmount:=0;
LastYearMonthAmount;

If I understand properly this formula will not compare the same range if report is run on 03/28/2019.

In this year it will take Date 03/01/2019 to 03/28/2019
But in Last Year it will take 03/01/2018 to 03/31/2018
Am I Right??


To meet client request I created something as this regarding rolling this year month and last Year month :
NumberVar Amount;
Amount:=0;
DateVar SMonth;
DateVar EMonth;
Datetimevar RunDate;
DateTimeVar StartDate;
RunDate:= {?Month Of Report};
SMonth:= Date(Year(RunDate),Month(RunDate),Day(RunDate));
EMonth:= Date(Year(RunDate),Month(RunDate),Day(RunDate));

If
({@InvoiceDate} >= dateserial(Year(sMonth),Month(sMonth)-1,Day(sMonth))
And
{@InvoiceDate}< dateserial(year(EMonth),Month(EMonth),Day(EMonth)))  
 then
Amount:={Invoice.Amount}
Else
Amount:=0;
Amount;





NumberVar LastYearMonthAmount;
LastYearMonthAmount:=0;

DateVar SMonth;
DateVar EMonth;

Datetimevar RunDate;
DateTimeVar StartDate;
DateTimeVar EndDate;

StartDate:= {?Month Of Report};
StartDate:= Date(DateAdd("m",-13,StartDate));
SMonth:= Dateserial(Year(StartDate),Month(StartDate),Day(StartDate));

EndDate:= DateAdd("m",1,StartDate);
EMonth:= Dateserial(Year(EndDate),Month(EndDate),Day(EndDate));

If
({@InvoiceDate}>= SMonth And {@InvoiceDate} < EMonth ) then
      LastYearMonthAmount:={Invoice.Amount}
Else
      LastYearMonthAmount:=0;
      LastYearMonthAmount;

Which date range I will have If I run this formula on 03/29/2019
As month February has 28 days.
TarasAsked:
Who is Participating?
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.

Raghavendra HullurSoftware DeveloperCommented:
Hi,

I think it will return 28th February 2018 as last year's start date even though start date is 29th March 2019.

You can create a test formula with content Date(DateAdd("m",-13,cDate(2019,03,29))) and check it.
TarasAuthor Commented:
Does it mean that DateAdd('m'-1,CurrentDate) or DateAdd('m',1, CurrentDate) will not always  give you the same date,  as current date could be  29,30,31 and February has 28 and leap year 29. The similar is with othere months that have 30 days(April, June, Sep..)

 When you subtracting or adding month or months in DateAdd function if the month that you are coming to has less days than month you are coming from  you come to the last day of the month not to that date from witch you are going back or forward as that date do not exist in that month.
Is that right?
Raghavendra HullurSoftware DeveloperCommented:
It always gives the last date of February if the current year's date is greater than 28 or 29 in leap year.
For other months, it will give the same date for previous year as in current year.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Raghavendra HullurSoftware DeveloperCommented:
31/03/2019 gives 28/02/2018
28/03/2019 gives 28/02/2018
28/03/2021 gives 28/02/2020
30/03/2021 gives 29/02/2020 because 2020 is a leap year.
TarasAuthor Commented:
It is not just March February issue it is May 31 April 30 and all other months that do not have 31 as Date going back or going forward does not matter.
Which date you will get if you add DateAdd('m'1,CurrentDate) if Current Date is 09/31/2019??
I am asking is that accurate? You are giving me only answer for March,- February. I think this issue is connected with other months that have this pattern. Can you confirm that?
Raghavendra HullurSoftware DeveloperCommented:
For all the days of all the months, if a particular date selected is not there in previous year, then the date will be the last date of that particular month.

In the same way, DateAdd('m',1,CurrentDate) will return the same date of the next month if the same date exists or it will be last date of the next month if the date selected is not available in next month.

For example: DateAdd('m'1,cDate(2019, 01, 31)) will return 28/02/2019 as February month has 28 days only.
If it's 30/04/2019 then the date returned will be 30/05/2019

Hope that clears your doubts.

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
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.