6 Replies Latest reply on Jan 25, 2019 10:03 AM by Michael Shaver

    Dashboard Date Filter

    Michael Shaver

      Good afternoon,


      I have created a dashboard that displays some basic financial information.  About half of the dashboard displays data relevant to a particular date that the user selects (daily sales, projected sales, etc).  The other half of the dashboard contains visualizations that display cumulative data over the past several years.


      I would like to configure the dashboard so that the date selected by the user also applies to the cumulative data.  When I apply the filter, to the visualizations they only end up showing the date selected -- which makes sense.  In order for this to work the way we imagine, it would basically need to filter the data so that anything less than or equal to the date selected is displayed.


      Is there a way that this one filter can perform both functions?  Choose the exact date for some worksheets but tell other worksheets to select everything equal to or less than that date?


      Thanks in advance for any thoughts/suggestions you have!!



        • 1. Re: Dashboard Date Filter
          Jim Dehner

          Good morning Michael

          the short answer is that filters limit the amount of data on one worksheet were they are applied - but you are using different worksheets to so you have some flexibility in doing something on one sheet and something different on another


          One way is to set a parameter (parameters are global and are available throughout the workbook) then develop different filters on the 2 sheets based on the parameter and the goals you want to achieve


          It may also be possible to use sets and set actions but I would have to see the twbx to be more specific



          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.

          1 of 1 people found this helpful
          • 2. Re: Dashboard Date Filter
            Michael Daddona

            Hi Michael,


            You can make your date filter a context filter and apply it to all sheets in the dashboard.

            Then create a calculation that gives you the Max date for all the data using fixed, something like:

            [Max Date] = {Fixed: Max(Order Date)}


            Fixed LOD's are filtered by context filters, so it will always be the max date your user selects in the date filter.


            Then you can apply this to your calcs, something like:

            [Dollars 12ME] = If [Order Date] >= DATEADD('Month',-12,[Max Date]) then [Dollars] End


            Or you can create a calc to show data for only the max date, something like this:


            [Max Date of Selection] =IF [Order Date]=[Max Date] then [Order Date] END


            I have uploaded an example of this workbook using superstore data for you to look at.  This method avoids having to update a parameter each time and keeps your date filter dynamic when you update your data.





            2 of 2 people found this helpful
            • 3. Re: Dashboard Date Filter
              Michael Shaver

              Thanks for the feedback Jim.  I've attached a twbx, which is a simplified version of the dashboard in question.  What I'm attempting to do is have the date filter also update the charts (basically remove all data that is past the date selected by the user).



              • 4. Re: Dashboard Date Filter
                Jim Dehner

                I could not open the file - the daa was not extracted before posting but see Mikes  solution above - very cool


                • 5. Re: Dashboard Date Filter
                  Michael Shaver

                  Thanks Michael!  I'll give this a try.

                  • 6. Re: Dashboard Date Filter
                    Michael Shaver

                    Thanks Jim -- reposted the file.  Also, I am attempting to implement Michael's suggestion.  Thanks!