DAX for Same Store Sales

[dkellogg]

I'm perplexed by a problem involving a same store sales calculation. I have a table of POS (point of sales) data, the important dimensions being the [Store name], and [Date] There several other dimensions so that there are multiple records per store. We are measuring [Units].<o></o>
I need to calculate the growth of same store sales quarter over quarter. Because we have stores joining and leaving over multiple quarters I therefore need to check to see if that store had sales in the previous quarter.<o></o>
The plain English version of the DAX would be: "Sum the units of each store that reported sales last period"<o></o>
I keep cycling through various calculate, sumx, previousquarter combinations, but no joy. Can anybody shed some light on the order of operations?<o></o>

 

[Fabio Lima]

Hey man, I had the same problem with the calculation you mentioned, what I did something else instead, which I'm pretty sure I can't be the best way, but it worked. I created another table in powerpivot to use as a flag in order to identify the current month, quarter or year, and also identify the previous month, quarter or year. So in the end I had a "T 0" for this month and a "T-1" for last month. Then I used the Calculated function with the filter "T-1" and T 0", which resulted in something like this:

=CALCULATE(SUM(database[sales_value]), tbl_calendar[T-1 T 0]="T 0") / CALCULATE(SUM(database[sales_value]), tbl_calendar[T-1])

Did you get I mean? Hope it helps.

Cheers

 

[powerpivotpro]

Hi guys, I saw this problem and couldn't resist digging in. I've got a solution to this that I will share on the blog tomorrow.

 

[powerpivotpro]

Here we go - let me know if this does/does not address the problem:

Calculating “Same Store” Sales in PowerPivot « PowerPivotPro