3 Replies Latest reply on Jun 6, 2018 10:16 AM by Michael Prokop

# Date Range Filter

I have a singular Date field that reflects a billing period ending date range.  I want to set a filter so that the workbook automatically opens to match the period end date that Today is currently in.

For example, we have period end dates that are weekly dates (6/1/18, 6/8/18, 6/15/18, etc).  These dates can continue well into the future and they are already in our database if something was entered in with a period end date some time later this year.  Today is 6/6/18, so it should filter to the period end date of 6/8/18, as it is higher 6/1/18, but under 6/8/18.  Once it is 6/9/18, if the worksheet is opened, it should filter to Period End Date of 6/15/18.  Hopefully I'm explaining this correctly.

I cannot post a sample workbook due to confidentiality.  I'm sure it's a simple calculation, but I can't seem to come up with something that works.

• ###### 1. Re: Date Range Filter

Assuming your date is in [Date]

Create a new dimension and type the following and drag it to your filter shelf

[Date] <= TODAY()

• ###### 2. Re: Date Range Filter

Won't that look at every other Period End Date prior to today though?  I want to look at only data between each period end date (in today's case, only data with Period End Date of 6/8/18).  Using the calculation above, it will check to make sure that we are at/below 6/8/18, but it will include all past data, which we don't want.

Here is an abbreviated listing of dates in the Period End Date field:

5/25/18

6/1/18

6/8/18

6/15/18

6/22/18

Because today (6/6/18) is greater than 6/1/18 and less than 6/8/18, it should pull only the data that has Period End Date of 6/8/18.

• ###### 3. Re: Date Range Filter

I figured it out myself.  In case anyone wants to know, I used:

DATETRUNC('week', TODAY()) = DATETRUNC('week', [PeriodEndDate])