How to Filter out Current Month

[whitkrieng]

Hi all,

I have a dataset that has this for example:

Activity_timeframe	Sample
5/1/2017	150
6/1/2017	100
7/1/2017	120
8/1/2017	140
9/1/2017	25

i want to remove 9/1/2017 because it isn't a full month yet.  

I tried in the "Filter" tool to put in: 

activity_timeframe < DateTimeFirstOfMonth()

It appears to not have worked.  Any guidance would be appreciated, thanks for your help.  

[nick_ceneviva]

What you have should work!  I think what is probably happening is that your Activity_timeframe column is not formatted as a date.  You will probably need to use the DateTime Parse tool to convert the field.

[danrh · Accepted solution]

It's likely a formatting issue.  I'd check if your dates are formatted as dates --- if not, use the DateTime tool to convert them.  Once you've verified your dates are good to go, then try adding DateTimeFirstOfMonth() in a formula prior to the filter and setting the Data type to Date.  I've had issues comparing Dates to DateTimes, so this might be the cause.

 

[whitkrieng]

Thanks for your response, Nick.

It was originally a string and I did convert it to a Date which I was able to confirm.  

Then I just used that formula in the filter, there were no outputs in the False output which there should have been if it is able to filter out the current month.  

[nick_ceneviva]

Very strange.  I just tried it out and got the same thing, so something weird must be going on with the date comparison.  This filter will work though:

DateTimeDiff(DateTimeFirstOfMonth(),[Activity_timeframe], "days")>1

[whitkrieng]

Oh this actually worked, some reason I have to create separately a FirstofMonth type dimension with the Formula tool.  Then used that within the filter, which is weird.  It's an extra step in the workflow but did work.  

I will try anyways your way too Nick just for learning purposes.  Thanks again for your help.  

[novice1]

hi, so i have data as below. this is total data for a year from 01-01-2018. i need to filter out only current month.

STORE_NBR	OD	YTD Volume	YTD Sales	Curent month
4126	38	53215	385231.3	01/07/2019 00:00
4127	38	27740	244259.42	01/07/2019 00:00
4131	38	55921	545563.56	01/07/2019 00:00
4135	38	39630	311489.01	01/07/2019 00:00
4136	38	30872	277936.72	01/07/2019 00:00
4137	38	47486	391730.53	01/07/2019 00:00

how can  i achieve this?

any advice would be much appreciated.

[novice1]

current month = 1st day of the current month

[DJT75]

It didn't work for me, when I use the formula in custom filter ToDate([date]) < DateTimeFirstOfMonth() 

it leaves out all with day 1. for example 10/1/2021 data is left in true. PLease advise