Filter between dates and dates to be dynamic

[alteryxlim]

Hi, I want to set a filter between two dates and the two dates to be dynamic.

So, essentially, 

Date 1 < data <  Date 2

Date 1 = start of the current month

Data = Target Completion Date (name of the column in my Excel where the data is located)

Date 2 = last day of the current month

I have the following filter formula but it doesn't recognize the "-1". 

I want to the filter to only show me the data between 01-March-2022 and 31-March-2022 (inclusive of the two end dates) and so I set the lower date constraint to March minus 1 for February. Obviously, Alteryx doesn't like my "-1" simple instructions. How would you modify?

ToDate([Target Completion Date]) <= ToDate(DateTimeTrim(DateTimeToday(),"lastofmonth")) AND ToDate([Target Completion Date]) > ToDate(DateTimeTrim(DateTimeToday(),"lastofmonth"-1))

[MarqueeCrew · Accepted solution]

@RolandSchubert  my motto:

Ready.  FIRE!  Aim.

Cheers,

Mark

[alteryxlim]

@MarqueeCrew How does your full filter formula look like?

Sorry, I'm still trying to catch up to speed and digest the proposed solutions from the Alteryx giants @atcodedog05 @RolandSchubert 

For now, I have used the solution from @RolandSchubert and it works but wouldn't mind to understand the LEFT function and how it's integrated into your full formula. 

[atcodedog05 · Accepted solution]

Hi @alteryxlim 

It would be something like this

Current month:

left([Target Completion Date],6) == left(DateTimeToday(),6)

Previous month:

left([Target Completion Date],6) == left(DateTimeAdd(DateTimeToday(),1,"month"),6)

The idea is year and month will stay the same throughout the month which 2022-03 or 2022-02

Hope this helps : )

[MarqueeCrew · Accepted solution]

@alteryxlim 

here is my full picture and workflow:

[alteryxlim]

Thanks @atcodedog05 @MarqueeCrew 

I guess this only works if the Month and Year stay the same for the duration of the 30/31 days. To have something dynamic (i.e when it's April, May, June.....Nov, Dec) and when it switches to Year 2023, the solutions from @atcodedog05 and @RolandSchubert would accommodate for this dynamicity. 

[atcodedog05]

Happy to help : ) @alteryxlim 

Cheers and have a nice day!