Filter Data to only use 'month to date' rows (including previous years)

Question

Hi all,

I have a table of data with one of the fields 'Date' containing dates for 2018 or 2019 e.g. "20/9/2018", "13/2/2019" etc.

Now there will be rows for any month of 2018 but for any 2019 rows the months only goes up to July (i.e. the current month).

I want to filter the data so that the output table contains everything except the July to December 2018 rows (i.e. the current month we are in today to December for the previous year) as well as exclude the current month we are in for the current year (i.e. July 2019)

This extraction is to be automated too. For example in future, when it's December 2019 the data is filtered to exclude December of 2018 and December of 2019.

[The aim down the track is to compare January 2019 with January 2018 and compare February 2019 with February 2018 etc... ]

I hope that makes sense!

Any help will be greatly appreciated! I'm new to alteryx so still getting used to it..

CRowlatt · Accepted Answer

Hi Serena3,

You should be able to use the formula tool and a DateTimeMonth function to create a month only column to return the month only value, this will give you a set of numbers 1-12 for each lines of data. Both Jan 18 and Jan 19 will be 1, Feb 18 and 19 will be 2, etc etc.

Then you need to just have a filter with a calculation like:

[Month] < DateTimeMonth(DateTimeToday())

All trues will have a month number less than the current month number. So you can compare a current year to date (until the end of previous month) with the same period last year.

Hope that makes sense. (and hope i've understood the question) 🙂

Chris

neilgallen · Accepted Answer

One filter tool is all you’d need with the following expression:

Datetimemonth([date]) < datetimemonth(datetimetoday())