3 Replies Latest reply on Mar 15, 2016 11:37 AM by Bill Lyons

    Filtering on Monthly, YTD, Last Year in same dashboard

    Madeline Coyne

      Hi,

       

      I have a monthly dashboard which is attached that shows data at a monthly, YTD and last year/YOY view. Since these are all different date ranges (ie. February 2016, January 2016+February 2016, February 2015) I am having a hard time creating 1 filter for the view.

       

      Basically for every monthly I want to be able to select the month and have every table update. This would be easy with just month and last year because I could have the individual tables be set on year then total filter on month but I can't figure out how to incorporate YTD into this.

       

      My solution last year was set a formula so the report updated on a monthly basis which worked in theory but was difficult if a previous month's view was desired.

        • 1. Re: Filtering on Monthly, YTD, Last Year in same dashboard
          Bill Lyons

          I hope I understand your request correctly. If I did, I think my attached solution (v9.0 as yours was) should be what you are looking for.

           

          Here are the steps I took:

          1. Showed and floated in the dashboard the "Month" parameter that you already created. (I also renamed it to [Month Parameter]. While this isn't necessary, it helps make it clearer and easier to build calculated fields to use it. You can rename it back if you like, and all the calculations will automatically update.)
          2. Created calculated fields, all of which return a boolean value:
            1. [This Month] = MONTH([Month]) = MONTH([Month Parameter])
            2. [This Year] = YEAR([Month]) = YEAR([Month Parameter])
            3. [This Year or Last Year] = YEAR([Month]) = YEAR([Month Parameter]) or YEAR([Month]) = YEAR([Month Parameter]) - 1
            4. [YTD] = MONTH([Month]) <= MONTH([Month Parameter])
            5. [Last Year] = YEAR([Month]) = YEAR([Month Parameter]) - 1
          3. I replaced each of your filters where YEAR([Month]) = 2016 with [This Year] = true
          4. I replaced each of your filters where MONTH([Month]) = January with [This Month] = true, with the exception of the YTD sheet, where I replaced it with [YTD] = true
          5. I replaced each of your filters where YEAR([Month]) = 2015 with [Last Year] = true
          6. I replaced each of your filters where YEAR([Month]) excludes 2014 with [This Year or Last Year] = true (based on the year-over-year usage)
          7. I also took the liberty of applying the "LOOKUP() trick" to hide the blank column in your YOY sheet. I thought that might be an unspoken question to enhance the view a little.

           

          I hope that helps.

          2 of 2 people found this helpful
          • 2. Re: Filtering on Monthly, YTD, Last Year in same dashboard
            Madeline Coyne

            this worked perfectly, thanks!