Date Range as a Formula

[Kristina_Toll]

HI,

I am struggling to create a formula vs a filter with range for these dates:

Today is 9-26-2023.  I'm reporting on month prior data from a year ago. 

So I need an END date of 8-31-2022 and a START date of 9-30-2021 

This is as close as I've gotten. 

[PaidDate] > DateTimeAdd(DateTimeToday(),-24,"months")
and
[PaidDate] <= DateTimeAdd(DateTimeToday(),-11,"months")

Any help is appreciated.

[EdwardCochrane]

Can you clarify a bit?

Can you simply use a filter or two to set a date range for greater than whatever the first date is, and less than the second date?

[Kristina_Toll]

I want to use this for the coming months as well so it's always a rolling range

So next month, I need it to be from 10-31-2021 - 9-30-2022

[binuacs]

@Kristina_Toll Try the below formula in the filter tool

toDate(DateTimeAdd(DateTimeAdd(DateTimeTrim(DateTimeToday(),'firstofmonth'),-1,'day'),-1,'Year'))

toDate(DateTimeAdd(DateTimeAdd(DateTimeTrim(DateTimeToday(),'lastofmonth'),0,'day'),-2,'Year'))

[Kristina_Toll]

@binuacs  Can I do this in 1 fell filter tool or must I do a Start and End date formula?  Just curious!

[TheMattLeonard · Accepted solution]

If I'm understanding you correctly I think these two expressions should work, though dates are always tricky because of uneven months/leap years, so Id definitely try testing it out with a few different dates.

End Date

DateTimeAdd(DateTimeAdd(DateTimeFirstOfMonth(),-1,"Days"),-1,"Years")

Start Date

DateTimeAdd(DateTimeAdd(DateTimeAdd(DateTimeFirstOfMonth(),1,"Months"),-1,"Days"),-2,"Years")

If you replace "DateTimeFirstOfMonth()" with a another date field with a few dates in there you should be able to test out how it'll work.

Hope this helps!

[binuacs]

@Kristina_Toll You can use this in the filter, just to understand i put it in the formula tool

[Kristina_Toll]

EXCELLENT!  appreciate you both!  

@binuacs -- your solution ALMOST worked!  <3

@TheMattLeonard  -- your solution worked.