1 Reply Latest reply on Sep 24, 2013 9:39 AM by Karen Satke

    YTD With Two Unique Dates

    Karen Satke

      Good Morning,

       

      I have a workbook with multiple tabs, each is filtered by one of two unique dates.  The purpose of the workbook is to see year to date data.  Since there are two different dates, the end user must filter by date on each tab, which they are forgetting to do after the first tab.

       

      I have two options, to create a filter/parameter that will filter both unique dates across the workbook, or I may set up a YTD calculated field which will automatically update the workbook based on TODAY().  (The workbook is currently set up for "Year to Month End," using month filters, but a year to date would work as well.)

       

      Problems with YTD- I have used YTD calculations before, but they only work for the previous year.  I would need to see the last 5 years YTD.

       

      Problems with Parameters- I've attempted to set up a parameter using month numbers and calc fields using the month datepart of the dates.  But the parameter can only have one month displayed at a time.  So I can view March, but not January, February, and March.

       

      I thought this was going to be an easy fix, but it has turned into a very complicated situation.

       

      I've made an example using Superstore Data.  Any help would be appreciated.

       

      Thank you,

      Karen

       


        • 1. Re: YTD With Two Unique Dates
          Karen Satke

          I ended up finding a solution.

          I was able to get the parameter method to work to include all months up to the parameter.  It was a simple calculated field: [Ship Date part] <= [Date Parameter]

           

          [Ship Date Part] being Datepart('month',[Ship Date])

           


          And the Parameter being:

          9-24-2013 11-35-52 AM.png

          I used the same method for Ship date and Order Date and added each filter to their respective tabs.

           

          Hopefully this will be useful for someone else!