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

Taras
Taras used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

Author

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?

Commented:
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.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

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

Author

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?
Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial