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 explain.
The Root Problem – Same Store Sales
Many retail businesses open and close stores throughout any given year. This creates a problem because it is difficult to determine if business growth is vertical (increased sales within existing stores) or horizontal (expansion of the store base). It is common to do analysis on “same store sales” where you only include stores that had sales for the entire period this year and also last year. There are quite a few posts already on this topic on PowerPivotPro.com, but this is a good opportunity for me to talk about SUMX and also DAX as a query language. I have reconstructed the scenario with some test data. You can download the workbook here if you want to take a look.
I started off with some base measures as follows:
A. Total Sales:=SUM(Sales[Extended Amount]) B. Total Sales LY:=CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date]))
There are a few different ways to work out sales last year, but I have used SAMEPERIODLASTYEAR above and this works just fine in this instance.