Dynamic Date Range Filter

[Karl_Spratt]

Hi Community,

I'm trying to get use a Dynamic Date Range Filter so I can pull the last 12 full months records when I run the WF, currently it's +4yrs data, and added to each month, for KPI's. 

SO I'm trying to use the Filter tool with this formula.  Can someone confirm if this is the best to use? 

My Date Column was in String, so I parse into YYYY/MM and the converted to Date/Time using customer.. But all my data shows first day of the month, So I'm worried on day 20, if I use the formula [YYYY-MM] <= ToDate(DateTimeAdd(DateTimeToday(), -365, "days")) I'm soing to be missing data. 

Can someone please advise?

TIA,

Karl. 

[BrandonB · Accepted solution]

Maybe use DateTimeFirstOfMonth() instead of DateTimeToday()? This would always pull the first of the month for your dynamic reference which I think fixes your issue. 

Also, shouldn't it be [YYYY-MM] >= DateTimeAdd(DateTimeFirstOfMonth(), -12, "months") so that your date column passed through the true is always after the date from 12 months ago?

[atcodedog05]

Hi @Karl_Spratt 

Since you dont have date in the Month field you are loading there is nothing you can do. But if you do have date field somewhere you can bring it in.

[Karl_Spratt]

Hi Brandon,

Thanks that worked, I used this

 [YYYY-MM] >= DateTimeAdd(DateTimeFirstOfMonth(), -12, "months")

>=  to as I need the last 12 Months data as I move forward, month to month.  Thanks for your help here,

Cheers,

Karl. 

[amruthas2]

Hi @Karl_Spratt 

Link for your future reference

DateTime Functions | Alteryx Help