How to obtain a consistent 90 days by week ending with a date filter

[D1234]

Hello,

I am currently working on a report in which I need to display a minimum of the most recent 90 days of information by the week ending date while maintaining complete weeks. 90 days comes out to be almost 13 weeks. Thus, I need to create a filter which will only give me complete weeks (starting on Sunday, ending Saturday).

Currently my filter looks like this:
[Date] between
   _add_days ( current_date, -90)
  and
   _add_days ( current_date, -0 )

The issue is that depending what day it is ran, complete weeks may not be possible. I then attempted to create a similar filter which looked like this:
_week_of_year([Date]) between
_week_of_year([  _add_days ( current_date, -90))
  and
  _week_of_year([ _add_days ( current_date, -0 ))

The issue with this however is that it displays information from previous years which are of the same week number. I cannot filter out those years because I still need it to return information from the previous year which happens to be within that 90 days requirement. Does anyone know of a way to fix this? It does not have to show exactly 90 days but it must show 90 at minimum to clarify.

Thanks

[BigChris]

The way I'd probably go about it is to work out the date for the most recent Saturday, then subtract 91 days off that. I haven't tested it, but it would be something along the lines of:

Code Select Expand


[YourDateField] between
_add_days( _add_days(current_date,-_day_of_week(current_date, 1)) ,-91) and _add_days(current_date,-_day_of_week(current_date, 1)-1)


[D1234]

This worked perfectly.

Thank you!