YOY Comparison for Most Recent Days

Comparing Same Day Sales YOY

In my last post, Showing Activity for Most Recent Days, I showed you how to find the most recent date for which you have “totals” data.  We found that whether it’s support calls or sales made each day, being able to retrieve this information quickly and efficiently can be extremely helpful.   By using the ROUNDDOWN AND MAX functions, as well as the very powerful SUMPRODUCT,  we were able to create a quick summary of the most recent days’ activity. The obvious next question?  How do those days compare with the same time last year.  In other words, using the most recent data, how are things going compared to last year?

We already have the first several pieces of the puzzle, the current year’s sales information.  To compare the data to last year, we simply find the totals for the same dates in the previous year.  We can determine these dates these dates fairly easy by using the CONCATENATE formula, along with the popular and well known MONTH and YEAR formulas.

If the comparison date is in A1, then:
CONCATENATE(MONTH(A1),”/”,DAY(A1),”/”,YEAR(A1)-1)
will give you the same date in the previous year.

But alas, of course it’s not that easy.  In many businesses, the day of the week makes a substantial difference.  Sales might spike on Wednesday or Friday, for example.  Or Monday might be the day on which most support calls are received.  In situations like that, it’s important to normalize the data, adjusting for day of the week.  To accomplish that, we need to compare the first (most recent) date in the date range with it’s equivalent in the previous year, but on the same day of the week.  (We only need compare the first date; the others can simply be a subtraction of one each time from that.)  To adjust, we’ll use the closest date before the current (comparison) date that falls on the same day of the week.

The formula is a little unwieldy:
CONCATENATE(MONTH(K4),”/”,DAY(K4),”/”,YEAR(K4)-1)+(WEEKDAY(K4,2)-WEEKDAY(DATEVALUE(CONCATENATE(MONTH(K4),”/”,DAY(K4),”/”,YEAR(K4)-1)),2))

But like you’ll hear me say, time and time again, working on formulas in small bites makes the process of building a successful formula a lot more manageable.  The attached file has a collapsed section to show you exactly how I attacked this project.  Feel free to open/expand it to see how all of the small parts come together to make the whole thing work.

So, let me talk you through the steps:

1. Find out on what day of the week the most recent date in our range falls.   Using the WEEKDAY function, it’s very easy.  The only required parameter to feed the function is the date.  You do, however, have some freedom in selecting on which day the first of the week falls, with the optional second parameter.  I use “2”, meaning I prefer Monday as my first day of the week.  Since B4 contains the most recent (current year) date, then, our formula to get day of the week is: WEEKDAY(B4,2)
2. Remember from above that, to get the same date in the previous year, we use the CONCATENATE function: CONCATENATE(MONTH(A1),”/”,DAY(A1),”/”,YEAR(A1)-1)
3. We then use the same WEEKDAY function we used in Step 1 on the previous year’s date to determine on what day of the week that date falls.  But we must first convert the now text string to a value that Microsoft Excel can recognize and ultimately convert to a date.  the DATEVALUE function does that for us: DATEVALUE(CONCATENATE(MONTH(A1),”/”,DAY(A1),”/”,YEAR(A1)-1)).
4. Using the WEEKDAY function on the converted (from string to value) CONCATENATE function, it is likely that the previous year’s date falls on a different day of the week.  To normalize, we simply add this difference (it may be a negative number) to what we calculated as the same date in the previous year.  For example, 3/1/2011 falls on a Tuesday (“2”, using Monday as the start of the week).  But 3/1/2010 falls on a Monday (“1”, using Monday as the start of the week).  The difference of 1 is simply added to the calculated date (3/1/10) of the previous year, which gives us 3/2/10 (a Tuesday).
5. We can now use this new calibrated date to total the sales– or support calls or whatever we’re measuring– on the previous year’s dates and compare those against the current year.

Comparing Apples to Apples now.

Example: Year over Year Comparisons made Easy