skip to Main Content

Same store sales continued using store openclose-dates with DAX

 
In a comment on Tuesday’s post, Cory asked the following question:

“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.”

Remember that in my first post, if a store had sales last year, and this year, I included it in the SSS (Same Store Sales) calc.

If we have columns in our data indicating the date a store opened (and when it closed), it’s a very rational thing to want to use that instead.

So I invented some new columns on my fictional Stores table:

I'm Going to Use These (Fictional) Store Open and Close Dates to Drive my PowerPivot Same Store Sales Calc

I’m Going to Use These (Fictional) Store Open and Close Dates
to Drive my PowerPivot Same Store Sales Calc

So, how do we do that?

First, I create a measure to calculate when a store opened.  I add this to the Stores table itself:

Read the Rest

Calculating same store sales using DAX

 

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.

Read the Rest