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.


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] =

(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] =

[Transactions Last Year] =

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],
         [Transactions Year Ago]>0 && [Transactions] >0

[Sales Last Yr – Stores Active Today and Last Year] =
CALCULATE([Units Sold Last Year],
         [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]


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.