Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Need Dynamic EOMonth Date

JaKael02
5 - 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

3 REPLIES 3
NicoleJohnson
ACE Emeritus
ACE Emeritus

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
Alteryx Alumni (Retired)

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
19 - Altair
19 - Altair

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