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

    Date Range Filter

    Michael Prokop

      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
          Yeok Song Ng

          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
            Michael Prokop

            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
              Michael Prokop

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

               

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