We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

Dynamic DateTimeFirstOfMonth

8 - Asteroid

I couldn't seem to find an answer to this but is there a way to the DateTimeFirstOfMonth function to be dynamic?


I'm trying to create a filter formula that says:


IF [BEGTIME]<DateTimeFirstOfMonth() THEN DateTimeFirstOfMonth([X]) ELSE [BEGTIME] ENDIF


where I want X to be a date pulled from the data. 


In other words, if condition is true then change BegTime column to first of month from another time column. 

9 - Comet

Hi @crtakacs 


Just a bit curious about your formula. If you have data that is say on the 22 May 2019 and it is currently 03 June 2019, do you want the formula to return 01 June 2019?


Would you want it to instead show the 01 May 2019 if the date in your data is 22 May 2019?


I have provided a workflow for each alternative for you. 



Going through the first formula, the DateTimeFirstOfMonth() will return the first date of the current month (June). The 2nd expression is the same formula you have included below.

First of Month 1.png

As you can see in the browse tool, it has changed the BegTime for every day before June to be 2019-06-01.


However, with the 2nd formula tool, the expression finds the first day of the month for every month. This is done by formatting the date in your data source to grab only the year and month and adding the 01 to the end. That means that if the date in your data source is in May, it will return the first of May. The 2 formulas have different outcomes depending on what you want. 


First of Month 2.png


Please let me know if you have further questions :)


- Andrew

18 - Pollux
18 - Pollux

Hi @crtakacs 


As well as @AndrewBanh's excellent solution, you can also use the DateTimeTrim(dt,t) function.  dt is the date you're processing and t is the string "firstofmonth" which will trim your date trimmed to the first of that month.  See the functions page for a complete description.