Alteryx designer Discussions

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

Need Dynamic EOMonth Date

Highlighted
Atom

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

Magnetar
Magnetar

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

 

 

Highlighted
Alteryx
Alteryx

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.

Highlighted
Castor
Castor

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

 

w.png

 

Dan 

Labels