Filtering Data Based on Previous Month

Question

Hello -

This may be a simple question.

I have an historical dataset that goes back multiple Years. I Would like to filter this data set to only show the previous Months Data in the workflow. The main reason I do not want to set a constant date is because every time the workflow runs, it needs to pull the previous months data. For example it is 05-09-2024, this workflow will need to pull all data from (04-01-2021) - (04-30-2024). If the date is currently (06-01-2024), this workflow will need to pull (05-01-2024) - (05-31-2024).

Please see attached for desired output.

Alteryx Community Example.xlsx

Luke_C · Accepted Answer

Hi @LorenzTaylor

Try this in a custom filter:

[Date] >= todate(datetimetrim(datetimeadd(datetimenow(),-1,'month'),'firstofmonth'))

and

[Date] <= todate(datetimetrim(datetimeadd(datetimenow(),-1,'month'),'lastofmonth'))

cmcclellan · Accepted Answer

Hi @LorenzTaylor

Put this is in a filter:

DateTimeAdd(DateTimeTrim(DateTimeToday(), 'month'),-1,'month') = DateTimeTrim([Date], 'month')

It's basically saying what you asked - take today's date (ie 5/10), trim it to month (ie 5/1) subtract a month (ie 4/1) and compare to your data that's been trimmed to month as well