4 Replies Latest reply on Jul 17, 2017 12:38 PM by Michael Someck

    Create an average of the last 52 weeks, based on a custom date range length

    Dermot Solon



      I have some data that I would like to create a rather specific average/benchmark from.


      My date consists of daily engagement totals - one number per day, showing the total number of engagements on that day.


      I would like my dashboard to have a date range filter, which causes two figures to populate:


      • one is the sum of engagements over the dates that I've selected (called "Engagements filtered by date" in the attached workbook)
      • the other is the average sum of engagements for the same length of time, starting on the same day of the week, for the preceding 52 weeks (called "benchmark engagements filtered by date", although the calculation is not complete)


      For example, if I pick the time period Monday June 19th to Wednesday June 28th, I would expect the "Engagements filtered by date" number to show me the sum of engagements for Monday June 19th to Wednesday June 28th, and the benchmark figure to show me the average of Monday June 12th to Wednesday June 21st, Monday June 5th to Wednesday June 14th, Monday May 29th to Wednesday June 7th etc, as far back as 52 weeks.


      In my workbook I've used a parameter to define the start and end dates, but I'm just as happy to use a date filter if that's easier (I figured the solution would probably end up requiring parameters).


      Thanks for any help or suggestions,


        • 1. Re: Create an average of the last 52 weeks, based on a custom date range length
          Michael Someck

          Hi Dermot,


          So I was able to come up with a solution. Though it may not be the most elegant, it does achieve what you are trying to do. Here are the steps I used (with a packaged workbook attached!).


          Step 1: Calculate how many weeks away each date in your data is from your Start Date. I called this field [Which Period] and used formula DATEDIFF('week',[Date],[Start Date]).


          Step 2: Calculate the length of the period you are looking at. I called this [Length of Period]: DATEDIFF('day',[Start Date],[End Date])


          Step 3: Determine whether each data in your data is the start of one of the previous 52 periods. The criteria for this are: (1) the day of the week of [Date] is the same day of week of [Start Date], (2) [Date] is before [Start Date], and (3) [Date] is no more than 52 periods away from [Start Date]. Putting this all together, I created a boolean calculated field called [Period Start Date] to determine whether a given date should be the start date for a previous period:


          Screen Shot 2017-07-10 at 4.36.16 PM.png


          Step 4: Create calculations for rolling sum and rolling average of engagements. For sum of engagements in periods, I used:


          Screen Shot 2017-07-10 at 4.38.14 PM.png


          This is essentially saying sum engagements from the [Length of Period] days below up through the given date. I used something similar (but using WINDOW_AVG) for Average engagements in period.


          Step 5 (optional): Calculate the end date for each period. Simple calculation was DATEADD('day',[MS_Length of Period],[Date]).


          Step 6: Putting it all together. This is where it gets a bit tricky. If you don't do it in exactly this order, it might not work out correctly. In this order, drag the following fields to the Rows shelf: [Date], [End of Period], [Period Start Date]. Make sure you sort [Date] descending in order of data order (so the latest date, in this case Sept 30, 2017, is at the top). Add the two measures we created in Step 4 to text.


          At this point, we are at the tab that I've titled "Before hiding". It won't look anything like what you want, since it's including a lot of "bad dates".


          Step 7 (last step!): We want to hide everything that we don't want. This requires 2 steps. First, right click the word "False" anywhere it appears in the "Period Start Date" column and select "Hide". This will hide all dates that aren't 1-52 weeks before the Start Date chosen in your parameter, as well as dates that aren't the first date of previous periods. However, note that these are still being used in the running_sum and running_avg calculations.


          Finally, we just want to hide that entire column of "True". To do this, right click on "Period Start Date" pill in the rows shelf and deselect "Show Header". The result should now be what you see in the tab titled "After Hiding" (I also added "Which Period" to the rows shelf so you can see how many weeks ago that period is).



          I hope that wasn't too confusing. I've also attached a packaged workbook which should hopefully make things a bit easier. Please let me know if you have any questions



          • 2. Re: Create an average of the last 52 weeks, based on a custom date range length
            Michael Someck

            I just realized that you are asking for something slightly different. You want the sum of the defined period and the average of the sums of previous periods. I misinterpreted and though you wanted the sum for each period and the daily average for each period.


            I'll see if I can figure out a way to do that, too!

            • 3. Re: Create an average of the last 52 weeks, based on a custom date range length
              Dermot Solon

              Thanks for taking a stab at it! I haven't been able to work out a solution and am starting to think it may not be possible... but I'll keep on trying out solutions and will post back if I work it out.

              • 4. Re: Create an average of the last 52 weeks, based on a custom date range length
                Michael Someck

                Hi Dermot,


                So I've been thinking a bit more about this, and I've come up with another solution. This one isn't as complicated in terms of formulas and calculated fields, but it does get a bit messy in that it essentially creates 52 copies of your data. Not sure this is what you're looking for either, but figured I'd share anyway!


                So the first thing I did was create an excel sheet with only one column titled "Period" that had 52 rows with values 1-52 (like this, except all the way down to 52):

                Screen Shot 2017-07-17 at 3.21.49 PM.png


                Then, I created a "join" with your original data source. I use the word "join" loosely, since all it's really doing is duplicating your data 52 times and adding row "Period" to each duplication. In other words, there is one copy where Period=1, another copy where Period=2, etc.


                To do this, right click your data source and click "Edit Data Source".


                Screen Shot 2017-07-17 at 3.24.55 PM.png


                Then, under "Connections:, click "Add" and find the excel workbook you just created. It should automatically pop-up with the question of how you want to join the 2 data sources. The key here is to create a "dummy" join that will force the duplication I mentioned above. To do this, I chose the option "Create Join Calculation" and simply put 1 for each formula:


                Screen Shot 2017-07-17 at 3.27.44 PM.png



                Screen Shot 2017-07-17 at 3.28.20 PM.png


                Once you do that for each sheet, you have the data source that you need (It shouldn't matter which kind of join you choose. I chose Inner).


                The first thing you want to do is make sure that "Period" is a Dimension rather than a Measure. The rest is actually pretty simple and requires just 3 formulas. The first 2 mark calculate the beginning and end of each of the previous 52 periods.


                START: DATEADD('day',-7*[Period],[Start Date])


                END: DATEADD('day',-7*[Period],[End Date])


                The final formula calculates the average of the sum of engagements for each period:


                AVG({FIXED [Period]: SUM(if [Date]>=[START] and [Date]<=[END] then [Engagements] END)})


                If you use this in your dashboard, you should get the desired outcome (Note that you also have to adjust your formula for Sum of Engagements Over Filtered Time Period to account for the duplication. One option is to divide by 52 because of the duplication. Another is to filter for just one value of Period in that sheet. I did the latter and filtered for Period=1 in that sheet.)


                Again, this isn't that efficient since it's effectively making 52 copies of your data, but it does give the result you're looking for. Happy to answer any questions if this wasn't clear, but I hope this helps!