7 Replies Latest reply on Aug 23, 2018 5:30 AM by Ashes Limbu

Comparing This Year Sales by Day of Week with Same for Last Year and Calculating Difference

Hi, all...We are trying to compare Sales for this year with Sales for last year for same day of the week and calculate the difference between the two in the same worksheet.  For example, this first Friday in 2016 was Jan 1 and the first Friday in 2017 was Jan 6.  Sales for Friday Jan 6, 2017 were 2760.75.  Sales for Friday Jan 1, 2016 were 2449.27.  The difference we want to see is Sales for Friday Jan 6, 2017 (this year) minus Sales for Friday Jan 1, 2016 (last year) = 311.28.  We need to see all data in a text table, not just the difference, for example like this:

Month          Weekday          2016 Sales     2017 Sales     Difference

January       Monday

Tuesday

Wednesday

Thursday

Friday               2449.47          2760.75          311.28

Saturday

Sunday

Attached is a workbook showing how far we have been able to get trying to do this.  Please see Sheet 2.

Any help would be most appreciated.

Thanks!

• 1. Re: Comparing This Year Sales by Day of Week with Same for Last Year and Calculating Difference

Hello Cherie,

My approach is as follows.

1. [Previous Year Day]

I simply get the date of 52 weeks ago with this calculation.

IIF(DATEPART('week',[Previous Year Day])<>DATEPART('week',[Bus Dat]),

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.

Regards

Lei

• 2. Re: Comparing This Year Sales by Day of Week with Same for Last Year and Calculating Difference

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

• 3. Re: Comparing This Year Sales by Day of Week with Same for Last Year and Calculating Difference

Hi,

Tried to compute using Lookup function,  (used table calculations to derive offset value), hope that helps.

• 4. Re: Comparing This Year Sales by Day of Week with Same for Last Year and Calculating Difference

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

• 5. Re: Comparing This Year Sales by Day of Week with Same for Last Year and Calculating Difference

Hi,

used "Lookup" table calculation and computed in table across to get previous value.

hope that helps

1 of 1 people found this helpful
• 6. Re: Comparing This Year Sales by Day of Week with Same for Last Year and Calculating Difference

Hello Sunil,

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 %

Also Cherie if you could share your final product will be really helpful.

Regards

Ashes Limbu

• 7. Re: Comparing This Year Sales by Day of Week with Same for Last Year and Calculating Difference

I am assuming this should give % difference

% diff = (SUM([Sub Tot])-([Sub Tot (copy)]))/[Sub Tot (copy)]

Kindly confirm