Need Dynamic EOMonth Date

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

Help

Accepted answers

NicoleJohnson

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!

DateCalcs.yxmd

All comments

NicoleJohnson

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")

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):

Take a look at the attached - hope that helps!

Cheers!

DateCalcs.yxmd

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

CurrentMonthDates.yxmd

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167