2 Replies Latest reply on Feb 11, 2019 1:43 PM by Perry Ho

    Week over Week Change with Dynamic Dates

    Perry Ho

      Hi there,


      I've been trying to get this to work for the entire week, but couldn't get it work. Hoping someone can help! My current solution is based on the tutorial here: (How to Calculate Week over Week Change of the Last Complete Week - YouTube )


      I'm trying to do a Week over Week % calculation that will change depending on the dates that I select.


      Right now, I'm able to get to get the % Change calculation, but unfortunately, the calculation only works if I select 2 or more dates so that the calculation can look up the prior date.


      Ultimately, my goal is to be able to select only a single date at any time and be able to calculate the prior week.


      Any help is greatly appreciated!



        • 1. Re: Week over Week Change with Dynamic Dates
          Andrew Bickert

          Hi Perry,


          One way of doing this is by duplicating the data source, creating a calculated field referencing one week in the future, and having the relationship between the two data sources be the week difference field (Kind of Confusing to explain so I will try and expand).


          1. Right Click on Data Source and Duplicate:

          2. Create Calculated Field on Primary Data Source: (Essentially just the As of Date formatted)

          DATETRUNC('day',DATEADD('day',0,[As at Date]))


          3. Create Calculated Field on Second Data Source with same name:

          DATETRUNC('day',DATEADD('day',7,[As at Date]))


          4. Unlink [As of Date] Dimension and Link on [As at Date1]

          5. Create the Percent Difference Calculation on the Primary Data Source:

          (avg([Value])-avg([Sheet1 (Test Data Dates) (copy)].[Value]))/avg([Sheet1 (Test Data Dates) (copy)].[Value])


          6. Change your Date Filter to a single value

          7. Add your Percent to the Detail, Format as a percent (Before you bring it to the Title), and add it to the Title



          This will only work if your [As at Date] is 7 days apart. It was for your example (For 12/28 and 12/21) so I figured it would work. If they have a different date range there are other ways we could do it but I thought this might get you started.


          1 of 1 people found this helpful
          • 2. Re: Week over Week Change with Dynamic Dates
            Perry Ho

            Sorry for the late reply - thanks alot!