add one month to data date

Question

I used this formula to roll-forward my data to the next month. However, when my Interface "Date" function's PriorDt was set up 6/30/2025, this formula returned 7/30/025 instead of 7/31/2025. I thought this formula is supposed to take into consideration the days in the month. Is there a way I can use the Interface Date function and roll the data forward at the end of each month? Thank you in advance.

DateTimeAdd("%Question.PriorDt%",1,"month")

alexnajm · Accepted Answer

I would use a DateTimeTrim function instead - for example, try DateTimeTrim(DateTimeAdd("2025-06-30",1,'months'),'lastofmonth')

https://help.alteryx.com/current/en/designer/functions/datetime-functions.html#datetimetrim

Qiu · Accepted Answer

@Falnangel 
This behavior is matching with the description of DateTimeAdd function, so using together with DateTimeTrim will be wise.
https://help.alteryx.com/current/en/designer/functions/datetime-functions.html#example-6982151

* Adding a unit does not change the value of smaller units. For example, adding hours does not change the value of minutes or seconds. Adding months does not change the day or time unless the resulting month does not have such a day. In that case, it goes to the last day of that month.