# How to sum measure value for all records between two dimension values

I have a cube, called `metric_cube`, with a measure of `Metric` and a dimension `Week` with the given output on SSAS from SQL Server 2014.

Current Output:

``````Week  Metric
----  ------
1      6
2      5
3      4
4      3
5      2
``````

I want to get the cube to output the sum of the `Metric` for all combinations of a from `Week` and to `Week`, where the from `Week` <= the to `Week`.

Desired Output:

``````Week(F)  Week(T)  Metric
-------  -------  ------
1         1        6
1         2        11
1         3        15
1         4        18
1         5        20
2         2        5
2         3        9
2         4        12
2         5        14
3         3        4
3         4        7
3         5        9
4         4        3
4         5        5
5         5        2
``````

I have tried using the same dimension twice, but I get a cross join error. I have also tried creating two dimensions and applying both of them, but all that does is duplicate the `Metric` value from the current output for all to `Week`s without consideration of the desired constraint for the from `Week` <= the to `Week`

Is it possible to use a data cube to achieve the desired output? If so, how?

First you need two cube-dimensions because you cannot use one dimension on more than one axe of a MDX-select. You can use role-playing dimensions, that means there is only one "Week" dimension in the database but it is used twice with different names for the cube.

Second each Week dimension has to be connected to a measure group. If you use only one measure group with one column "WeekId" you can only get a measure not null when you select the same week from both dimensions.

One solution is to create a facts-table with the cartesian product of all combinations Week1Id, Week2Id and a value precalculated to the sum you wanted.

Or you use two measure groups, one for "FromWeek", one for "ToWeek" and store a rolling sum of the metric, here in one table:

``````Week  SumFrom   SumTo
1       0         6
2       6        11
3      11        15
4      15        18
5      18        20
``````

and create a calculated

``````MEMBER [measures].[result] AS ([ToWeek].CurrentMember, [Measures].[SumTo]) - ([FromWeek].CurrentMember, [Measures].[SumFrom])
``````