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

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:

The formula is quite straightforward:

[Open Date Measure] =
LASTDATE(Stores[OpenDate])

Note:  I could have used FIRSTDATE() just as easily as LASTDATE(), since this measure only makes sense in the context of a single store.  And in that case, the two functions will return the same date, since we’ll only have one row of the Stores table in that context.

This does what I need it to do when I put StoreID on rows:

Then I create a measure that calculates “how many days has a store been open?”

[Store Age at Period Start] =
INT(FIRSTDATE(Calendar[Date])-[Open Date Measure])

(I wrapped that INT function around it because Excel kept trying to format the result as a date rather than an integer).

For this to make sense, I need to have something from the Calendar table on the pivot, so for now, here’s Year-Month on a slicer:

So in January of 2008 (2008-01 on the slicer), store #1 had been open 2376 days, and store #9 had an age of –154, meaning that it was still 154 days away from opening.

Now, to determine if the store was open last year at this time, I add another measure:

[Was Store Open Last Year] =
IF([Store Age at Period Start]>365,1,0)

The Two Highlighted Zeroes Indicate Those Stores
Were NOT Open a Year Prior to August 2008

Yes, in that measure it might be “safer” to use 366 or 367 rather than 365 to account for leap years and those rare “off by one” problems, but you get the idea.

And in Cory’s case, where he requires that a store be open for a full 15 months, and not merely open 12 months ago, he can change that number to be 365+92 or something similar.

Store Close Date

Now I add two more measures to deal with Close Date:

[Close Date Measure] =
LASTDATE(Stores[CloseDate])

[Is Store Closed] =
IF(LASTDATE(Calendar[Date])>=[Close Date Measure],1,0)

In July 2009, Three of Our Fictional Stores Had Closed

Tying it All Together

Now we can calculate “sales that count” for this year and last year, just like we did in the last post, but this time we use a different set of tests in the FILTER function:

[Current Sales – Respect Open Close Dates] =
CALCULATE([Units Sold],
   FILTER(Stores,
          [Was Store Open Last Year]=1 && [Is Store Closed]=0
   )
)

[Same Store Sales Last Yr – Respect Open Close Dates] =
CALCULATE([Units Sold Last Yr],
   FILTER(Stores,
          [Was Store Open Last Year]=1 && [Is Store Closed]=0
   )
)

Only the highlighted portions differ from the original method used in Tuesday’s post.

Now it’s just a simple “(New – Old) / Old” measure:

[Same Store Sales vs Last Yr – Respect Open Close Dates] =

(
   [Current Sales – Respect Open Close Dates]-
   [Same Store Sales Last Yr – Respect Open Close Dates]
) /
[Same Store Sales Last Yr – Respect Open Close Dates]

And we test it out in a new pivot that just has Year-Month on rows:

Hey That’s a LONG Measure Name!

Yes.  Yes it is.  I prefer to give my measures VERY descriptive names so that 6 months from now I know what the measure actually DOES.  That makes the pivots unwieldy to look at though.

No worries.  Just select that measure header cell in the pivot and type over it:

The field list changes to reflect that, but only in one place:

Custom Measure Name Appears in Drop Zone but Not in Measure List – Genius!

I LOVE THIS.  I can quickly tell “what’s what” this way.  I could write pages on how much I like this, but let’s skip that and just leave it at “I really think this was a great design decision” (and no, *I* had nothing to do with it – this happened after I left MS).

Download the Workbook

Download the Updated Workbook Here

One Advantage of the “Did We Have Sales” Approach

As a parting comment, one of the hidden advantages of Tuesday’s method is this:  if a store was open last year, but it didn’t even have a department that sold fruit until last month, the Open/Close date method will tell us that the store should “count,” but really, it shouldn’t.

The “did we have sales” method, however, automatically accounts for this, on a per-product basis.  That’s pretty cool. 

Of course, if certain products don’t sell very often even when they ARE offered for sale, the “did we have sales” method can again be misleading – sometimes, not selling something means does NOT mean that you weren’t trying.

Every business situation is different, and it’s therefore good to have many different methodologies you can choose from.  There is no “one size fits all” so I’m happy to cover alternatives and contrast the tradeoffs.  Neither is “better” – you have to choose.

Stated differently, you GET to choose