Dynamic DateTimeFirstOfMonth

[crtakacs]

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. 

[AndrewBanh]

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.

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. 

Please let me know if you have further questions :)

- Andrew

[danilang]

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.

Dan

[bkclaw113]

I never realized the First of Month function only gave the current month.