My approach is as follows.
1. [Previous Year Day]
I simply get the date of 52 weeks ago with this calculation.
2. [Previous Year Day Adjusted]
IIF(DATEPART('week',[Previous Year Day])<>DATEPART('week',[Bus Dat]),
DATEADD('week',-1,[Previous Year Day]),[Previous Year Day])
In case, the week numbers of [Previous Year Day] and [Bus Date] are different, I adjusted it.
3. A problem left
From the screenshot, you can see there are some "strange" dates. This happens in years which have 53 weeks.
Hi, Lei...I figured out a way to compare the sales by day of the week. Now I just need a way to calculate the difference in a added column to the right of the two day columns. Please see the first two worksheets in this updated workbook. I need to combine these on a single worksheet. Thanks, Cherie
HI, Sunil...This is close, but I need to compare same weekday and calculate the difference. So, I need to compare Friday, Jan 6 2017, to Friday, Jan 1 2016, and Saturday, Jan 7 2017 to Saturday, Jan 2 2016, and so on. Please see attached workbook where I was able to set the correct comparison dates up. Now if I can just add the calculations to this worksheet, I will have it. Is there a way to do the same calculations for the difference in the worksheet labeled "Sales Comparison by Day of Week"? Thanks, Cherie