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
1 of 1 people found this helpful
please check attached workbook.
used "Lookup" table calculation and computed in table across to get previous value.
hope that helps
Your solution works like a charm, Today I am at the same juncture as Cherie.
With your lookup you have achieved the difference no but how do I show this in %
Looking forward to your response.
Also Cherie if you could share your final product will be really helpful.
I am assuming this should give % difference
% diff = (SUM([Sub Tot])-([Sub Tot (copy)]))/[Sub Tot (copy)]