Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 MDX Sum Over multi selection in a time dimension Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, March 30, 2014 5:01 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Sunday, April 26, 2015 6:09 AM Points: 101, Visits: 222
 Good evening to all.I try to have an help it is possibileMy 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)AND2) I both select Year=2014 Month = 3 Days from 2 to 29 (i want sales amount of 1-28 OF march 2014So 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.000ps. 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.
Post #1556304

 Permissions