Calculating “Same Store” Sales in PowerPivot

 

Calculating Same Store Sales in PowerPivot

Another one from the forums

I love the PowerPivot forum over at Mr. Excel for many reasons, one of which is that it gives me useful, real-world inspiration for blog posts.  Recently, I saw someone asking about how to calculate “same store sales.”

Simply put, same store sales means comparing sales of ONLY stores that are open today and were also open at the same time last year (or last month, last quarter, etc.).

In other words, it’s a “year over year” (or month over month, etc.) comparison measure that only looks at stores that were open last year, and are still open today.


Highly Recommended:  A Stores Table

As a prerequisite, I recommend that you import or create a Stores table.  Doesn’t have to be fancy, it can even be a single column.

image

I Created This Single-Column Stores Table
via Copy/Paste

And relate it back to your Sales table.

The “Raw” Formulas

Here are the measures used to calculate the raw (unfiltered, all stores) sales:

[Units Sold] = SUM(Sales[QtySold])

[Units Sold Last Year] =
CALCULATE([Units Sold],SAMEPERIODLASTYEAR(Calendar[Date]))

(Note that this could be calculated MANY different ways, including using the Greatest Formula in the World).

[Raw Growth vs last Year] =
IF([Units Sold Last Year]=0,BLANK(),
   ([Units Sold]-[Units Sold Last Year])/[Units Sold Last Year]
)

Transaction Count

The way I decided to determine if a store was open last year was to determine if it had any transactions.  This is slightly more reliable than testing for [Units Sold] > 0, since in rare cases you might have returns (refunds/exchanges) that offset all of the products you sold.

[Transactions] =
COUNTROWS(Sales)

[Transactions Last Year] =
CALCULATE([Transactions],SAMEPERIODLASTYEAR(Calendar[Date]))

If this measure returns 0 or BLANK for a store, I can assume that store was not open last year.  There are alternatives of course, and I may delve into those in a future post.  But this is a great place to start.

Filtering Sales to Stores that Were Open Before & Remain Open Today

OK, if both [Transactions] > 0 AND [Transaction Last Year] > 0 for a particular store, that means it’s a store that we want to “count.”

So we use that to generate new versions of Current Sales and Sales Last Yr:

[Current Sales - Stores Active Today and Last Yr] =
CALCULATE([Units Sold],
   FILTER(Stores,
         [Transactions Year Ago]>0 && [Transactions] >0
   )
)

[Sales Last Yr - Stores Active Today and Last Year] =
CALCULATE([Units Sold Last Year],
   FILTER(Stores,
         [Transactions Year Ago]>0 && [Transactions] >0
   )
)

Make sense?  We filter the Stores table to only include rows (stores) for which both current transactions and last year transactions are > 0.

Final Measure

Now it’s really straightforward:

[Same Store Sales vs Last Yr] =
([Current Sales - Stores Active Today and Last Yr]
  – [Sales Last Yr - Stores Active Today and Last Year]
)
/[Sales Last Yr - Stores Active Today and Last Year]

image

Growth Looks Much Worse When We Just Compare Same Store Sales!

So the raw growth and same-store growth measures tell very different stories.  In this case, that’s due to the way I manufactured data for this example (using functions like RANDBETWEEN). 

In the real world of course, this can happen when you open a bunch of new stores – skewing your overall numbers higher.  In that case, same store sales can show you that per-store sales have been falling, a fact that is masked by the broader totals. 

There are other ways to do this as well.  At Pivotstream we often account not just for stores opening and closing, but also which stores stocked which products, whether prices increased, etc.  But fundamentally it’s still the same idea.

What do you think?  Any particular variations you’d like to see?

Download the workbook here.

***UPDATE:  Version That Respects Store Open and Close Dates

If you’d prefer to use precise open/close dates for your stores rather than “did we have sales,” see the follow-on post.

6 Responses to Calculating “Same Store” Sales in PowerPivot

  1. Cory Nielsen says:

    Great post and timely for myself. I have been working on a Y/Y SSS calculation for a project I’m working on, but I’m hitting a bit of brick wall. Here is what I’m trying to do… Each store in my list has a start date. I want to use the stores start date to determine if it should be included in the same store calculation or not. Y/Y SSS calculation regardless if there are sales for that store this year or last for the specific date range I’m looking at (typically by month for last 13 months). The other twist to my SSS calculation is the store must be open for at least 15 months before it is included in my SSS Total. I hope my question makes sense. Thanks for any guidance you or anyone can offer.

  2. dmonder says:

    How do I get a copy of this for Excel 2013? I cannot convert the data model due to an error connecting to the VertiPaq engine.

    • powerpivotpro says:

      In my experience, that error is fleeting. If you click off of the pivot, then click back on the pivot, and try to upgrade again, it works.

      It’s weird how it fails the first time then succeeds the second time but that has been my experience.

      • dmonder says:

        I have tried clicking off the pivot table and back on, being in the pivot table, not in the pivot table…nothing seems to work. Am I supposed to have access to some server somewhere?

      • dmonder says:

        I tried this in Excel 2010 and received the same error, and was able to see the data model using your trick. I am still not able to see the data model in Excel 2013.

Leave a Comment or Question