Add Months

[rahulsingh12]

Hi,

Is there a way to restrict addmonths from taking previous month.

E.g: addmonths('01-02-2017',-3) should give '01-01-2017' not '01-12-2016'

Regards

[Or]

AddMonths(01-FEB-2017,-3) shouldn't give January 1 2017 OR December 1 2016. It should give November 1 2016. You may want to clarify your goal.

[rahulsingh12]

Yeah Meant that only,

My only concern is it should not give 2016 but if months are going beyond the current month then should give the minimum month i.e. for that year

Regards

[Mark_Little]

Hi,

Is this a restriction you are trying to add for some calculation or mentioned above, it would 01/11/2016?

If you want to make sure it does go back a year wou would need something like.

IF(YEAR(ADDMONTHS(DATE,-3)) <> YEAR(ADDYEARS(DATE,-1)), YEAR(ADDMONTHS(DATE,-3)), YEARSTART(DATE))

Mark

[Or]

RangeMax(YearStart(YourDate),AddMonths(YourDate,-3)) should do the trick.

[rahulsingh12]

I will never get the min date for the selection here.

I need to get the l3m data here and this will give me max of the date I would need to get the min as well.

Regards,

Rahul

[Or]

You didn't ask for the minimum. You asked for "Three months ago, unless that's last year, in which case January 1st of this year".

[rahulsingh12]

I tried something like this:

sum({<Description={'Actual'},MonthYear={">=$(=vL3MLYMinYear)<=$(=vL3MLYMaxYear)"}>}[Volume(HL)])

where vL3MLYMinYear=

IF(MONTH(DateNew)<=2,DATE(YearStart(AddYears(DATE(DateNew,'DD-MM-YYYY'),-1)),'DD-MM-YYYY'),

IF(MONTH(DateNew)>2,

DATE(RANGEMin(AddYears(DATE(DateNew,'DD-MM-YYYY'),-1),AddYears(AddMonths(DATE(DateNew,'DD-MM-YYYY'),-2),-1)),'DD-MM-YYYY')))

vL3MLYMaxYear=

DATE(RANGEMax(AddYears(DATE(DateNew,'DD-MM-YYYY'),-1),AddYears(AddMonths(DATE(DateNew,'DD-MM-YYYY'),-2),-1)),'DD-MM-YYYY')

But the some of sales comes as 0 in this case.

Regards,