Dynamic Date Range: Rolling 12 Months - Prev Month

[pennyra]

I'm trying to build a dynamic date range to filter on a rolling 12 months - end of the previous month.

For example: Today is 9/21/2020, so I want my date filter to show 9/1/2019 - 8/31/2020. 

Thanks for your help. 

[wdavis · Accepted solution]

Hi @pennyra 

The attached workflow should allow you to do this. You would need to update this with your own Field for the current date.

You can then use a combination of the DateTimeAdd and DateTimeFirstOfMonth to create the 2 new dates to filter the data on.

Let me know if this works for you.

Kind Regards,

Will

[ConnorK · Accepted solution]

Hi @pennyra,

To dynamically filter for these dates you may try using the "DateTimeFirstOfMonth()" and the "DateTimeAdd()" formula to find the first day of the current month and then subtract a year from that date. I have attached an example of this building out two field (Start_Date & End_Date) which can then be used to filter on in a Filter tool.

I hope this helps!

[echuong1]

You can built your filter ranges using datetime formulas. From there, append the range value to each record and create a filter.

[HomesickSurfer]

Hi @pennyra 

1) Create [FIRST DATE] formula (Date type:Date):

DateTimeAdd(DateTimeAdd(DateTimeToday(),-12,"months"),1-DateTimeDay(DateTimeAdd(DateTimeToday(),-12,"months")),"days")

2) Create [LAST DATE] formula (Date type:Date):

DateTimeAdd(DateTimeToday(),0-DateTimeDay(DateTimeToday()),"days")

3) Apply Custom filter to records:

[DATE]>=[FIRST DATE] AND
[DATE]<=[LAST DATE]