SQLServerCentral » Data Warehousing » Analysis Services » MDX Sum Over multi selection in a time dimensionInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralFri, 28 Apr 2017 22:26:56 GMT20MDX Sum Over multi selection in a time dimensionhttps://www.sqlservercentral.com/Forums/FindPost1556304.aspxGood evening to all.
I try to have an help it is possibile
My name is Mauro i am writing from Italy...and i am little new about MDX SSAS 2008 R2.
I have a SALES Cube that contains a [Measures].[Sales Amount] and a time dimension [Calendar].
Time dimension CALENDAR is like this :
DIMENSION [Calendar]
HIERARCHY [DataAAAAMMGG]
First level of Hierarchy [Year]
Second level of hierarchy [Month]
Third Level of hierarchy [Day]
I use Excel 2013 to browse SALES Cube.
I select Calendar Dimension in a multi value mode .
That is:
1) I both select Year=2014 Month = 3 Days from 1 to 28 (i want sales amount of 1-28 OF march 2014)
AND
2) I both select Year=2014 Month = 3 Days from 2 to 29 (i want sales amount of 1-28 OF march 2014
So i choose different periods in time dimension , and i want to calculate difference of sales between first selection 1) and second selection 2)
Example: (Sales 2014-3- from 1 to 28 ) - (Sales 2013-3 From 2 to 29)
I do not know if i did good to use parallel period...
I tried but i do not what is the best to do that...
If i drag [Measure].[Sales] it works but i need to calculate a delta from sales of different years , same month , and different range of days...as described before.
I wrote a calculate member but it calculate very wrong values...:
'SUM
(Existing [Calendar].[DataAAAAMMGG].[Month].Members,
([Measures].[Sales] - (ParallelPeriod([Calendar].[DataAAAAMMGG].[Year], 1), [Measures].[Sales])))'
I hope someone help me...
I am sure someone did something of similar in his own cubes..
To be clear if Sales Amount of 2014-3- from 1 to 28 = 100.000 euro and Sales Amount of 2013-3- from 2 to 29 = 80.000
i need to have difference 100.000 - 80.000 = 20.000
ps. Notice i select multivalues in time dimension and not the same...that is days range different (1-28 and 2-29)
I hope you can help me thanks.
Regards.
Sun, 30 Mar 2014 17:01:47 GMTmaretix