by Matt Allington I was helping a friend out recently with an interesting problem. It all started with a SUM( ) that wasn't behaving. It quickly became a SUMX( ) problem but evolved into a DAX Studio/Query problem. Let me…
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
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:
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.