The Same Store Sales calculation is very common in the retail and restaurant business
A very common metric in the restaurant industry is same store sales? What is that?
Let us say that you own a restaurant chain that has 5 stores. In January you open a new store, so now you have six.
When you look at your end of January report, you notice that your sales are up 5% year over year. Except you would expect that because you are comparing 6 stores to only 5 stores last year. So a common metric is same store sales, which says "give the the year over variance but only for the stores that have been open for a year". In the above example, the new January store would not be included in the comparison. And in this case, I might find that my same store sales year over year is actually negative.
How can this be implemented in a SSAS cube?
This can be a tricky calculation because the cube has to be smart enough, when looking at last year's numbers, to filter out the stores that are not yet flagged as "a comp store". This can be accomplished by the following steps:
- Adding a date calculations dimension, as discussed here
- Adding a new dimension called "Store Comp" which has "comp", "non-comp" and "all stores" as the members
- Adding a hidden dimension which tracks the store's comp status one year ago.
- Modifying the MDX script so that all "last year" calculations look to the hidden dimension for the comp status and not the current comp status
If that sounds confusing, it somewhat is! But we have implemented this approach with numerous companies and it provides the necessary analytics. The beauty of this approach is that it is really simple from the user perspective. All they have to do is select "comp" from the Store Comp dimension and then use the date calculations dimension and all "prior year" calculations will filter the data appropriately.