11 Replies Latest reply on Dec 12, 2017 9:07 AM by Casey Gallaher

    Comparing sales for parameter date ranges

    Casey Gallaher

      Alright guys I am going to try my best to see if I can explain what I am trying to do. So I have a sales report I need to create for the sales team. They decided that they did not want sales to be by year but rather they can pick which date ranges they use. So I created four parameter controls:

      Start Date Previous: this allows the user to pick the start date of the previous year they would like compare- Example: they pick 11/01/2015

      End Date Previous: this allows the user to pick the end date of the previous year they would like to compare- Example: they pick 11/01/2016

      Start Date Now: this allows the user to pick the start date of the now sales: Example-they pick 11/02/2016

      End Date Now: this allows the user to pick the end date of the now sales: Example- they pick 11/02/2017

       

      Thus this allows the user to select whichever date ranges they would like to compare.

      Now comes the part I am stuck at, I need to show a graph that has the previous sales from the date range the user picked compared to the now sales that the user picked broken down by customer. I attached test data and a test workbook that shows how I have the sales for each time period on separate worksheet but I would like them to be on the same work sheet with a dual axis on the graph.

        • 1. Re: Comparing sales for parameter date ranges
          Shinichiro Murakami

          Casey,

           

          Is it beneficial that user pick 4 different parameters in this case?

          I think it's more reasonable to make users select "This year"'s range and previous ranges just follows?

           

          Do you have special rules to pick different range?

          For me it's more natural

          When user pick 11/01/2016~2017/10/31

          Previous year is filtered without using parameters as 11/01/2015~2016/10/31

          Why the range is 1 year + 1 day and not 365(or 366 in leap year) days ?

           

          Regards,

          Shin

          • 2. Re: Comparing sales for parameter date ranges
            Casey Gallaher

            Shin,

             

            This is how I originally had the report that the user would just pick "this year" and the previous range follows. However, my boss asked if it would be possible that he could pick all the ranges so I told him I would try to see if that was possible. Also that is a typo on me the range should just be 365 days!

            • 3. Re: Comparing sales for parameter date ranges
              Joe Oppelt

              Casey -- It's not a problem that your user wants to fill out 2 ranges.  You can just work with those params to pick out starts and stops.


              Just a suggestion on that though.  Often a user wants to pick out different ranges because he might want to look at a given quarter, or month or year, etc.  And that might be what he was thinking of when asking to fill out 2 ranges.  I don't like giving the user two separate ranges to request.  What's the value in him looking at Jan-June of 2016 compared to March-August of 2017?  Giving him two ranges can end up with that.

               

              But he might want to look at Jan-mar 2017 against Apr-jun 2017, and that has a reasonable business purpose.

               

              In my shop I give the user a param for a start date for the current period, and then give him another param that lets him pick what he wants to do.  (Quarter-over-quarter, this-quarter-vs-year-ago, YOY, etc.)  Then I create my own start- and end-range values for the two comparison periods in my own calcs.

               

              I'm just throwing that out there for consideration.

              • 4. Re: Comparing sales for parameter date ranges
                Joe Oppelt

                And what I do with those ranges is this:

                 

                I create two measure calcs -- one for the prior period and one for the current period.  Each works like this:

                 

                // Prior

                IF [Transaction Date] ... is within the prior range ... THEN [Value] END

                 

                Ditto for current.


                The calcs load up the values only within the respective ranges (and the out-of-range transactions have NULL.)  Then SUM([Prior]) will display all the prior data, and SUM([Current]) does the same respectively.

                 

                Further, I create a [Display Date] dimension.  If transaction is in the current range, then [Display Date] = [Transaction Date].  And if transaction is within the prior range, then I add whatever is needed (based on the periodicity calc that tells me what to compare) to move the transaction date to the current dates.  That is to say, if the user is looking at Q-over-Q, then I add a quarter to the transaction date, or if he's looking at year-ago data, I add a year.

                 

                Then I run two measures on a chart that uses [Display date] as the axis.  Both lines display along the current date, but I color them differently.  I can still display transaction date in the tooltips for the prior data, and that displays correctly.

                • 5. Re: Comparing sales for parameter date ranges
                  Shinichiro Murakami

                  HI CAsey,

                   

                  mmm.

                  If user intentionally pick different range between this year and previous year, (for me) the comparison does not make sense.  May be that's intentional. however,

                  other disadvantage of your approach is that you need to change the parameter range for all four each time the data range updated.

                  I don't think  that's maintenance able.

                   

                  Thoughts?

                   

                  Shin

                  • 6. Re: Comparing sales for parameter date ranges
                    Casey Gallaher

                    Joe Oppelt ahhh thank you, I am going to go with your consideration!

                    • 7. Re: Comparing sales for parameter date ranges
                      Joe Oppelt

                      Ping back with any questions as you implement this.

                      • 8. Re: Comparing sales for parameter date ranges
                        Casey Gallaher

                        Hello agian,

                        Okay so I have 3 parameters, select a start date, select a number range, and select a date field. So select a start date would be 11/01/2017 then they would select how far back they want to go in this case 1 then they select a date field in this case year. I then have this calculation:

                         

                        DATEADD([Please Select a Date Field:],-([Please Enter a Number]),[Please Select Start Date:]) which finds the end date.

                         

                        However, I noticed that you don’t use 3 parameters when you were describing it and I was wondering what your calculation would be for the start and end date?

                        • 9. Re: Comparing sales for parameter date ranges
                          Joe Oppelt

                          In my case the user can do quarter-over-quarter, or quarter-this-year-vs-quarter-last-year, or month-over-month, or month-this-year-vs-last-year, and a whole bunch of other combinations.  (We also at times have the concept of trimesters, so those are 4-month chunks...)  Therefore I have a date parameter just like your [Please Select A Start Date], (which in my case is the start of the current period the user wants to examine) and then behind the scenes I build my own [End of Current Period] calc, as well as [Begin...] and [End of Prior Period] calcs.

                           

                          Thus, if he selects one of the "Month" param values, my current data calcs start with the selected start date, and end with the last day of the selected month.  And to create the Prior range calcs I would either subtract a month or a quarter or a year from the selected start date to get the beginning of prior, depending on what he selected, and the same for end of prior.

                           

                          Then, from my earlier post I make [Prior] and [Current] measure calcs like this:

                           

                          // Prior

                          IF [Transaction Date] ... is within the prior range ... THEN [Value] END

                           

                          So specifically it looks like this:

                           

                          // [Prior]

                          IF [Transaction Date] >= [Begin Prior Period] and [Transaction Date] <= [End Prior Period] THEN [Value] END

                           

                          And the same for [Current].

                          1 of 1 people found this helpful
                          • 10. Re: Comparing sales for parameter date ranges
                            Joe Oppelt

                            So if you have more concise comparison needs (YOY, QoQ, MoM only), then your calc is far cleaner than what I have to do here.

                            • 11. Re: Comparing sales for parameter date ranges
                              Casey Gallaher

                              Thank you, I got it working with your help!!