Date Range Formula Filter YTD

[pennyra]

I am trying to create a dynamic YTD filter for my data based on the calendar year (beginning Jan. 1). 

if today >= 11th of the month, then filter YTD - Prev Month

Else if today <=10th of the month, YTD - 2 Prev Months

Thanks in advance for your help. 

[DavidP]

Hi @pennyra 

Have a look at the worfklow below.

I've assumed that you want start of year to last day of previous month minus either 1 or 2 months.

Let me know if this is incorrect and I can adjust the formula.

Based on this you can use a filter tool for formula [Date]>=[Start Date] AND [Date]<= [End Date]

[CharlieS · Accepted solution]

Good stuff as always, @DavidP 

I too would suggest preparing a min and max date fields that can be used for filtering the data. Just to throw out an alternative, I understood the max date a bit differently. Here's the expression I came up with:

IF ToNumber(DateTimeFormat(DateTimeToday(),"%d"))>=11
THEN
DateTimeAdd(DateTimeFirstOfMonth(),-1,"day")
ELSE
DateTimeAdd(DateTimeAdd(DateTimeFirstOfMonth(),-1,"day") ,-1,"month")
ENDIF

Since today is the 13th, it'll put the end date as the last day of last month (2020-07-31) by going back one day from the first of the current month. Otherwise, if this were 4 days ago, it would have returned a month before that (2020-06-30). 

[DavidP · Accepted solution]

Ah yes @CharlieS, I was in two minds. Just realised that there is a vital typo in my formula anyway, 

if DateTimeDay([DateTimeNow])<=10 then ...

[pennyra]

Thank you so much for the quick response - I was able to make this work with a formula and filter tool. 

[pennyra]

Thank you so much for the help!