Filter formula to include the prior Year/Month from the current date

Hello All, I've seen a few other similar topics on here but haven't found one that worked for me but I'm hoping it might be fairly simple...

I have a field in my data that reflects YYYY-MM based on a formula of a more complicated date field. I'd like to filter on this field to include only the prior month from the current date. So if the current date is today (05/02/2024) the filter would bring in everything where that field reflects 2024-04.

Expression

Accepted answers

Qiu

@cgoad61
We can use the combination of DateTImeToday(), DateTimeAdd and DateTimeformat to arrive the "Previous" Month as below.

0503-cgoad61.yxmd

usmanbashir

@cgoad61 - please see attached workflow. Hope this helps!

This is slightly different approach than @Qiu. I used this within Filter tool:

[DateField] = Left(DateTimeAdd(DateTimeFirstOfMonth(), -1, 'months'), 7)

**You can use either DateTimeToday() or DateTimeFirstOfMonth() since only extracting the first 7 characters of the date to get the Year-Mo. I oftentimes forget the syntax for Alteryx date time format, hence why I used the Left() function. This is my typical approach to getting Year-Mo.

PriorMonthOnly.yxmd

All comments

Qiu

@cgoad61
We can use the combination of DateTImeToday(), DateTimeAdd and DateTimeformat to arrive the "Previous" Month as below.

0503-cgoad61.yxmd

usmanbashir

@cgoad61 - please see attached workflow. Hope this helps!

This is slightly different approach than @Qiu. I used this within Filter tool:

[DateField] = Left(DateTimeAdd(DateTimeFirstOfMonth(), -1, 'months'), 7)

PriorMonthOnly.yxmd

cgoad61

Thank you both! Either option works although for this case I ended up using Qiu's.

Qiu

@usmanbashir
Thank you for letting me the function "DateTimeFirstOfMonth()" 😁

brwright

Thank you @Qiu! May I make one suggestion/request in future posts? Screenshots are great, but for formulas, could you include the text for copy/paste?

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703