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.

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 7 Comments

  1. Cory Nielsen

    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.

    1. powerpivotpro

      Sounds like a great topic for tomorrow’s post, please stand by :)

  2. dmonder

    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.

    1. powerpivotpro

      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.

      1. dmonder

        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?

      2. dmonder

        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.

  3. alex

    How could you calculate CAGR or CQGR? This formula works fine – [SalesLast]/[SalesFirst] , but I have tried to add in the end of the formula “^(1/4)-1” to new formula = [SalesLast]/[SalesFirst]^(1/4)-1 and it gives error: “An argument of function POWER has the wrong data type or the result is too large or too small. I did double check in normal excel, and it works fine and return normal values, 10-15%. Do you have any idea how to fix it?

Leave a Comment or Question