0

Year Over Year on a Pivot Table

Does anyone have any insight or methods on doing Multiple Years Measures and Year Over Year calculations on a pivot table when the dates need to be specified in the columns. Also a possible calculation or method to compare day of the week rather than the exact date from prior years?

7replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • When you talk about "the dates need to be specified in the columns", are you talking about 

    The 1st day of the 12 week of this year is to compare with the 1st day of the 12th week from the last year?   Does this mean that the data will be aggregated by days?

    Reply Like
  • Hi Dylan,

     

    In need to be flexible with the date type, whether its the Day Date or Week Start Date, the issue is the year is part of that date. If you are trying to group data together with those date types as the columns on a pivot table, no prior year data will appear. I need a way of keeping the date specific only to the month and the day, reformatting the existing date structure does not solve this as the year is still implied though not visible.  

     

    Also would be the option to have these dates line up day of the week year over year rather than the calendar date itself.

    Reply Like
  • Hi Mark,

    This video may help:

    - https://youtu.be/qZNrY4x5yWI

    It shows and explains how to design YOY analysis in Pivot table and analyze sales by 

    - Quarter and Category 

    - Week, Day and Category

    Thanks,

    Anurag

    Reply Like
  • Hi Anurag,

     

    Thank you for the suggestion and video. I have followed the instructions but for some reason my previous year/period orders are appearing in the future year 2020. I noticed the same structure in the YouTube video, but upon following the instructions I was not able to get them to merge as you did. I am using the Week Start Date of our Gregorian Calendar which is joined to the Order table as well as it's alias with the Year Ago Date.  I've gone over this several times, but can't figure out what I have done wrong.

    Reply Like
    • Mark Samuels  Can you share you schema that includes the join from both tables alias tables to the Date.date.date table.  Also, you may want to check the data, namely to ensure that that you don't have a 2020 in the fact table...

      Reply Like
  • Hi Seth,

    Attached is the schema in question, Alias is OrdersPrevYear.

    Reply Like
  • I have found the solution to this issue, thanks for your help.

    Reply Like
Like Follow
  • Status Answered
  • 2 mths agoLast active
  • 7Replies
  • 52Views
  • 4 Following