Unable to filter future months

Im trying to identify items with end of month dates. In this scenario, i want to filter only items on 30th of June and 31st of July.

There is 1 line item thats falls under the 31st of July however i tried using the formula shown below but it didn't work.

I am able to get items from before June but not after.

Any advise?

Developer

Developer Tools

Date Time

Bug

Accepted answers

KarolinaRoza

Hi @JinOnn ,

I am not sure if your formula works correctly for previous months, if I apply:

[DateTime_Out]=todate(DateTimeAdd(DateTimeLastOfMonth(),-1,"month")) -> receiving: 2021-05-30 - not correct, as last May day is: 2021-05-31

[DateTime_Out]=todate(DateTimeAdd(DateTimeLastOfMonth(),-2,"month")) -> receiving: 2021-04-30 - OK

Please try:

[DateTime_Out]=todate(DateTimeTrim(DATETIMEADD(DateTimeToday(), 1, "month"),'lastofmonth')) OR

[DateTime_Out]=todate(DateTimeAdd(DateTimeLastOfMonth(),0,"month"))

it should work.

Karolina

All comments

jamielaird

Hi @JinOnn ,

Try this, which tests if a date is the last of the month by checking if date + 1 day has a different month:

DateTimeMonth(DateTimeAdd([DateTime_Out],1,"days"))
!=
DateTimeMonth([DateTime_Out])

KarolinaRoza

Hi @JinOnn ,

in the second part of your formula you are getting : 2021-07-30 ( 2021-06-30 + 1 day).

Please try below:

[DateTime_Out]=todate(DateTimeAdd(DateTimeLastOfMonth(),0,"month")) OR

[DateTime_Out]=todate(DateTimeTrim([DateTime_Out],'lastofmonth'))

regards,

Karolina

KarolinaRoza

Hi again,

Actually below formula is enough for each month last day:

Karolina

[DateTime_Out]=todate(DateTimeTrim([DateTime_Out],'lastofmonth'))

JinOnn

Still not able to get what I want.

Im using the filter function to get the last day of the month for June and July.

However, the formula (as attached) was not able filter data in July.

However, when i use -1 or -2 as my interval, it works! I am just not able to use 1 to capture July's date

jamielaird

Hi @JinOnn ,

Have you tried the solutions posted by @KarolinaRoza or I?

DateTimeMonth(DateTimeAdd([DateTime_Out],1,"days"))
!=
DateTimeMonth([DateTime_Out])

[DateTime_Out]=todate(DateTimeTrim([DateTime_Out],'lastofmonth'))

If these don't give you the answer you want, can you clarify what you are trying to achieve?

JinOnn

Karolina is close! However, I want to only get the end of month date for June and July. Karolina's formula allows me to get all the end of month date from previous months.

What I am looking for is as shown above. The date time add formula works for -1 or -2 as my interval but it doesn't work for future months.

KarolinaRoza

Hi @JinOnn,

I copied explanation of this function from Alteryx Help webpage.

I hope it will be useful for you.

I am glad that I could help you .

Regards,

Karolina

DateTimeTrim

DateTimeTrim(dt,t): Removes unwanted portions of a date-time and returns the modified date-time.

Parameters

dt: Date-time data expressed as a selected column or a specified date-time value between quotes.

t: Trim type. Options include:

firstofmonth: Trim to the beginning of the month (this does the same as month).
lastofmonth: Extend to one second before the end of the last day of the month.
year: Trim to midnight on January 1st.
month: Trim to midnight on the first day of the month.
day: Trim to the day (i.e., midnight). This converts a date-time to a day with a time of zero (not a date).
hour: Trim to the hour.
minute: Trim to the minute.

Trimming a date-time does not round the returned value. For example, the time 15:59:59 trimmed to the hour becomes 15:00:00, not 16:00:00.

Example

DateTimeTrim("2016-12-07 16:03:00","year") returns 2016-01-01 00:00:00.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703