2 Replies Latest reply on Aug 21, 2017 9:02 AM by Jim Dehner

    Filter for Previous 12 Months

    Jordan Laughlin

      I am trying to build a dashboard where I can filter to a specific month in one graph, and the other graphs will auto-filter to the previous 12 months. Unfortunately, my data is full of confidential HR information, so I can't attach the workbook, but a snapshot of the dashboard is attached.


      The Turnover graph is composed of data from a rolling 12 months, so the August 2017 data point is composed of July 2016 - August 2017 data. This is why the filter is set to 24 months, but is only showing 12 months of data in the Turnover graph.


      Is there a way to make a roll-up filter that is more intuitive to the end user? I'd like them to be able to select 24 months and actually show 24 months rather than 12. Also, it would be great to show the bottom two graphs with the rolling 12 months. So if I filter Turnover to the previous 12 months (August 2016 - August 2017), then the bottom two graphs will filter to August 2015 - August 2017. Similarly, if I choose a data point on the Turnover graph (June 2017), it will show the 12 months previous in the bottom 2 graphs (July 2016 - June 2017).


      I apologize for the confusing question with a lack of workbook attachment, but this confusion is what I'm trying to avoid with my stakeholders. Thank you for any help!

        • 1. Re: Filter for Previous 12 Months
          Carl Slifer

          Hi Jordan,


          I'm not really sure what your question is in here. If you want to compare dates - check out this old blog post of mine.



          If this is a filtering woe then you may want to look into using Action Filters. You can also make the single filter in the top right corner apply to multiple worksheets. You can do this by clicking on it's drop down option within your dashboard and you'll see the option 'apply to worksheets'.


          Hope this puts you on the right path. Not sharing your workbook is fine - but it seems rather basic. Why not mock it up using a random dataset?


          Best Regards,

          Carl Slifer

          • 2. Re: Filter for Previous 12 Months
            Jim Dehner


            please see the attached - it is a superstore example - I'll give you the formulas that you can adjust to meet your need


            First the parameters are straight forward






            this formula determines the individual values by  month to include (I used sales)

                      if DATETRUNC('month',[Order Date])<= DATETRUNC('month',[enter starting month and year]) and

                      DATETRUNC('month',[Order Date])>=

                       DATETRUNC('month', DATEADD('month',-[number of months to rollup], [enter starting month and year])) then

                      [Sales] else 0 end


            This one rolls them up

                      if min([Order Date])>

                      dateadd('month',-[number of months to rollup],[enter starting month and year])

                      and min([Order Date])<=[enter starting month and year]then

                      sum([Rolling months to include sales])+PREVIOUS_VALUE((0)) else 0 end


            and this forms a Boolean filter that you can use to decide the range to display



            here is the result





            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.