Filtering to previous month

[richard_gardner]

Good Afternoon. I'm trying to create a dynamic filter that will filter the date/time to only the previous month. The data comes in as DateTime. I'm sure this is simple, but I can't seem to come up with a solution that works.  Suggestions?

[danrh]

You could use a Custom Filter with the following, where [Dt] is your date field:

DateTimeFormat([Dt],'%m%y')=DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,'month'),'%m%y')

EDIT: Just to clarify, I'm including the year portion in case you're looking at data that covers more than the last 12 months. If you have more than 12 months worth of data and compare to just the month, you'll end up getting all data that happened in July, regardless of whether it was July of this year, last year, or 10 years ago.  Hope this helps!

[adm510]

edit: never mind, I misread the question and thought you wanted the prev month and earlier :)

[estherb47 · Accepted solution]

Hi Richard,

Custom filter is the way to go. If you're only concerned with the month, then drop the %y from the above solution.

Here's my suggestion:
DateTimeFormat([date],"%b")=DateTimeFormat(DateTimeAdd(DateTimeNow(),-1,"month"),"%b")

This pulls the month out of your Date field, and compares it to the month before (the DateTimeAdd function here subtracts one month from today's date).

When the month turns over, this will still work to capture the previous month. Hope it helps!!

Best,

Esther

[MarqueeCrew]

Hi all :)

DateTimeDiff(DateTimeTrim(DateTimeStart(),"Month"),DateTimeTrim([Tran Date],"Month"),"Month")

That formula will count the number of calendar months between when the workflow starts and the transaction date.  0 is returned for the current month and 1 for the prior month.

If I wanted something equal to last calendar month in a filter, it would be:

DateTimeDiff(DateTimeTrim(DateTimeStart(),"Month"),DateTimeTrim([Tran Date],"Month"),"Month") = 1

The formula reads as:

Calculate the difference in MONTHS between the start date in the format of:  YYYY-MM-01 00:00:00 and the transaction date in the same format.

I like it...

Cheers,

Mark

[SanK]

Here is a very similar way to EstherB47's method, but taking care of the year as well.

DateTimeAdd(DateTimeFirstOfMonth(),-1,"month")= DateTimeTrim([yourDate],"month")

You never know, eventually mutiple years will creep in and stuff up your logic.

[Baradomas]

@SanK

DateTimeAdd(DateTimeFirstOfMonth(),-1,"month")= DateTimeTrim([yourDate],"month")

This worked the best, thank you!

[Ben_Kohler]

Wonder if I can use this same formula in order to get the previous month of data from a year prior. I'm assuming I would just input "-1"year" after that... 

[MichelleMitchellLutz]

This worked like a charm! Thanks for sharing.

[SanK]

DateTimeLastOfMonth() picks up the last day of the month. 

If today is the 17th of Feb 2022, then

DateTimeLastOfMonth() gives you 2022-02-28 (Date formated)

DateTimeTrim(DateTimeLastOfMonth(),"month") gives you 2022-01-01