How can I compare the number of transactions in the first weeks or months of different stores

I need to make a comparison of the number of transactions per week and month of different stores since its opening date. The problem is that the stores has a different opening date.

So, The idea is to compare the number of transactions in the first weeks or months

That's what I have:

Canal Key 1

   Recount FactTransactions

                              Month1-2015 Month2-2015 Month3-2015 Month4-2015 Month5-2015 Month6-2015

 Type of store     Cod Store 

Spain     
         2          Store 1                                              5         6        10
                    Store 2      10          20            40            50        60       85        
         4          
                    Store 3      31          45            100           315       441      625              
                    Store 4                                10            20        32       45
Portugal
        1       
                    Store 5                                                                 12

That's what I want to obtain (comparing different stores since its opening date):

Canal Key 1

   Recount FactTransactions

                              Month1 (or Week) Month2 Month3 Month4 Month5 Month6

 Type of store     Cod Store 

Spain     
         2          Store 1       5              6      10
                    Store 2       10             20     40     50     60       85        
         4          
                    Store 3       31             45     100    315    441      625              
                    Store 4       10             20      32    45
Portugal
        1       
                    Store 5                                                                 12

That's my data model:

```
FacTransaccion: Measures Recount FactTransaction / Amount / Amount discount / Unit Price

DimDate: Hierachy Date (YYYYMMDD) / Month / Month Name / Week of Month / Week of year / Year (It's the same Dimdate than AdventureWorks

We really need more detail on your measures and dimensions to give you an exact answer on this, but since you are using SSAS a few general rules apply.

If you add more detail I can expand my answer to be in line with your data model.

Since you want to compare the number of transactions you should have a measure showing the number of transactions.

As you want to compare them over time you would also need a Time Dimension with a hierarchy.

If you have both those prerequisites you can resort to MDX to calculate sums and differences of your measure over your dimension hierarchies and members.

In essence what you need to try is to construct a set and sum your measure over that set.

Sum({[opening date]:[opening date+2weeks]},[Measures].[Number of transactions])

The hard part in your problem will be constructing the set however. And that's the part we can't answer without your data model and some sample data.

As you say you have different start dates for different stores you could use a property on the store dimension indicating it's start date and use that to construct a set to sum over.

A great resource to get started with MDX is the Stairway to MDX series on SQL Server Central.

You should look at functions like PeriodsToDate.

You also have the option of resorting to VBA functions and could use that to use DateDiff and DateAdd if you can't get there with pure MDX.

You can even write your own SSAS Stored procedure if all else fails.

StackExchange Database Administrators Q#112062, answer score: 5