3 Replies Latest reply on Nov 12, 2015 7:00 AM by Chris Fergus

    View past months up to current day

    Chris Fergus



      I'm creating a dashboard, and I'd like to create a parameter that will allow the user to choose whether to view Entire past months or past months up to the most recent day of data in the current month. For example, if they choose Month to Date and the most recent day of data is November 10, then they would see November's data, October data up to the 10, and so on. It would also be great if they could choose how many previous months they would like to look back at.




        • 1. Re: View past months up to current day
          Shawn Wallwork

          Chris this is totally doable (but not by me at the moment). If you don't get the answer by tomorrow, ping me and I'll set it up for you.



          • 2. Re: View past months up to current day
            Carl Slifer

            Howdy Chris,


            Maybe I can be of service to you. I've attached a workbook. In the explanation below I've assumed you are familiar with parameters and with using calculated fields off of these. If not we can discuss them, or as always you can dissect the including workbook to y our heart's content. I'm using Superstore for simplicity.  This workbook has two different sheets one for each example that we could use to filter our dates. Before we discuss the differences let's chat about the common parts.


            One of the issues we face with using a regular filter based on DAY(Order Date) < DAY(Today) is that we will get three options when we use a quick filter. We will get condition met, condition not met, and all. In our case what we really want is All and condition met, if we include condition not met it just looks kind of funky. Never fear! Parameters to the rescue!


            We created a Parameter (Day Cut Off Parameter) because my creativity is lacking today and then we created a calculated field (Day Cut Off) in order to give us more control of the situation.  The parameter allows us to tell a calculated field what we want to do in this case, include all days or only those less than the current day of the month.


            The calculated field that uses this parameter will always return 1 if by using the parameter we say all the days and if the parameter says only choose days of the month less than today's date it returns 1 when this is true and 0 if not. Finally we filtered on this calculated field to return only 1 and wa-la!


            Now let's chat about the differences between them.

            The differences are in one sheet we can use a relative date which allows your end-user to change this to look at the past X years or months, etc. And in the second we used another parameter and another calculated field so that the user is only ever using months and the calculated field simply checks if the dates are within the last X months and includes it.


            I sincerely hopes this helps you and solves your dilemma and I've included another calculated field as a label just to show the max day of the month showing for each month.


            Carl Slifer


            • 3. Re: View past months up to current day
              Chris Fergus



              This is perfect. Thanks a bunch, you're a lifesaver.