I need to compare MTD numbers (and other time periods such as last 4 weeks, YTD) to last year, but only want to include sales in the current period for stores that were open for the full period last year.
For example:
This year MTD: 12/1/2014 - 12/3/2014
Last year MTD: 12/1/2013 - 12/3/2014
Stores 1 and 2 were opened on 11/1/2013, but store 3 opened on 12/3/2014 so it's data will be omitted from the calculation.
Store Date Units
1 12/1/2013 3
2 12/1/2013 6
1 12/2/2013 2
2 12/2/2013 8
1 12/3/2013 4
2 12/3/2013 7
3 12/3/2013 11
1 12/1/2014 3
2 12/1/2014 6
3 12/1/2014 9
1 12/2/2014 4
2 12/2/2014 6
3 12/2/2014 9
1 12/3/2014 4
2 12/3/2014 7
3 12/3/2014 10
CY LY [+/-]
Store 1: 11 9 [+2]
Store 2: 19 21 [-2]
Store 3: 19 28 [N/A] --N/A because it wasn't open for the full MTD period last year.
I came up with a method to accomplish this with a stored procedure, but I'd like to utilize MDX calculations (similar to the Time Intelligence Wizard output) right in the cube.
Has anyone worked on something similar?