Unable to filter future months

[JinOnn]

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?

[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

[JinOnn]

Hey Karolina!

I think you're close , however in this scenario I only want the dates for the month of June and July.

Is there another syntax I can try?

[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.

[DawnDuong]

Hi @JinOnn 

You can combine what @KarolinaRoza  suggested with the DateTimeMonth function to limit the months to June and July only.

E.g.

[DateTime_Out]= DateTimeTrim([DateTime_Out],'LastOfMonth')

AND

(DateTimeMonth([DateTime_Out]) = 6 OR DateTimeMonth([DateTime_Out]) = 7)

[KarolinaRoza · Accepted solution]

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