Need Dynamic EOMonth Date

[JaKael02]

Good morning -

First post here.  So let me know if I do anything incorrect.

I have a sales fact table with daily dates.  I'm trying to filter out all the days from the most previous month-end to current.  For example:  Today is 12/9.  I want to return all the dates from (12/1 to 12/9).  I've tried a handful of similar forum posts but nothing seems to work correctly. 

Is this normally this difficult in Alteryx or is their a simple solution that is eclipsing me?

Kind Regards,

Jim

[NicoleJohnson · Accepted solution]

You should be able to do this in 2 tools (or possibly even 1)! See attached solution illustrating both!

2 tool option (if you have today's date already in your data somewhere):

1. Formula tool to derive the first day of the month: DateTimeTrim([TodayDate],"month")

2. Generate Rows tool to create a record for every date between StartDate and TodayDate, using StartDate as your Initialization point and then DateTimeAdd([Date],1,"day") to iterate until you get to TodayDate

1 tool option (will generate all dates without an input if you're just wanting to do this every time based on today's date when workflow is run):

1. Generate Rows tool starting with DateTimeTrim(DateTimeToday(),"month") to get you start of the month based on today's date, and then same process as above, using DateTimeToday() as your ending condition.

Take a look at the attached - hope that helps! 

Cheers! 

NJ

[TonyA]

In @NicoleJohnson 's workflow, you can also use DateTimeFirstOfMonth() in the initialization of the one tool solution. This gives you the first day of the current month.

[danilang]

Hi @JaKael02 

Another option here is to use a filter tool that is configured to pass only the dates that are in the current month

DateTimeMonth([Date]) = DateTimeMonth(DateTimeToday())
And 
DateTimeYear([Date]) = DateTimeYear(DateTimeToday())

"Is the month of your date field [Date] the same as today's month"  Same for the year

Dan