cancel
Showing results for 
Search instead for 
Did you mean: 


Rolling Months - Previous 12

bella21
NiCd Battery

Is there a good way to calculate previous 12 months, where the lookback is on the calendar month as a whole? E.g. if today is January 15th, 2021 and I want the previous 12 months excluding this month, I would get January 1, 2020 - December 31, 2020. 

I have tried the following formulas which do not isolate calendar months;

IF(AND(datediff(endofmonth(@DateField@), endofmonth(today()), "months") >0, datediff(endofmonth(@DateField@), endofmonth(today()), "months") <=12

IF(AND(datediff(@DateField@, today(), "months") >0, datediff(@DateField@, today(), "months") <=12))

 

Labels (1)
2 Replies
shyam
DataRobot Employee
DataRobot Employee

Hi Aaron! 

You were very close. Here's how I would do it. 

AND(
  datediff(@Date@,  endofmonth(@Today@), "months") <13, 
  datediff(@Date@,  endofmonth(@Today@), "months") >0
  )

shyam_0-1611850743844.png

Regards,

Shyam

0 Kudos
bella21
NiCd Battery

I switched to <13 / >0, but that didn't work either.  It was giving me the same result where I had values from 2019 coming through. 

Solution was to calculate

1) PriorYearDate: DATEADD(DATEADD(ENDOFMONTH(today()),-1,"months"),-365, "days")

2)  Then if the date fell between prior year and month-end prior month: IF(AND(@receivedDate@ >= @PriorYearDate@, @receivedDate@ <= DATEADD(ENDOFMONTH(today()),-1,"months")),"12MM"

 

0 Kudos