## MDX ParallelPeriod Function - HELP

 Author Message maretix Old Hand Group: General Forum Members Points: 303 Visits: 234 Hi to all.I have a calculated measure in one of my cubes.This is named DELTA SALES AMOUNTIt calculates Parallelperiod about SalesAmount.This calculate measures is based on the dimension Calendar that is time Dimension.I have a Hirarchy named DateAAAAMMGG that is composed of there levels:Year Month DayThe MDX Expression is that :'([SalesAmount] - (ParallelPeriod([Calendar].[DateAAAAMMGG].[Year]),[SalesAmount]))'If i select in my Excel's Pivot Table , the level Year (Ex 2013) and Month (Ex. May) and Day (Ex. 24)ALL IT WORKS GOOD.In fact i have as result salesamount about prior year 2012 the same month of may the same day 24..But if i select the level Year (Ex 2013) and Month (Ex. May) and more Days (Ex. 20-21-22-23-24)I RECEIVE no results ...(Ex. Null)I expected to have a result salesamount of prior year 2012 the same month of May and the sum of salesamount of days 20-21-22-23-24....instead nothing ...Where is my mistake ???I think it is possible to have results selecting more days using PARALLELPERIOD with a TIME DIMENSION ..Why not ???Or i have to use different MDX functions ???Regards in advance. Jonathan Mallia Ten Centuries Group: General Forum Members Points: 1223 Visits: 1290 Hi,You should try something on the lines of:`SUM([SalesAmount]) - SUM((ParallelPeriod([Calendar].[DateAAAAMMGG].[Year], 1, [Calendar].[DateAAAAMMGG].CURRENTMEMBER),[SalesAmount]))`Let us know how it goes...Jon maretix Old Hand Group: General Forum Members Points: 303 Visits: 234 Hi to all.Thanks for your kind reply and your interest to my problem.I tried to write as you suggest to me ,,but it does not solve problem'SUM([SalesAmount]) - SUM((ParallelPeriod([Calendar].[DateAAAAMMGG].[Year], 1, [Calendar].[DateAAAAMMGG].CURRENTMEMBER),[SalesAmount]))'Writing this expression it works only if i select:Year = 2013 and Month = 5 and only ONE DAY Day = 24If i select MORE ONE YEAR or MORE ONE MONTH or MORE ON DAY...i receive Null valueor better i receive only the value of SUM([Num Entrate]) because all part of expression with PARALLELPERIOD does not able to do a result...Any suggest ???I will try to understand better...Thanks...:-) maretix Old Hand Group: General Forum Members Points: 303 Visits: 234 I think to have found a solution.I need to iterate over each member of a set using Existing MDX FunctionIn this way we can solve the problem of multi select in a Time Dimension...Existing [Calendario].[DataAAAAMMGG].[MeseVendita].Members, ([Measures].[Num Entrate] - (ParallelPeriod([Calendario].[DataAAAAMMGG].[AnnoVendita], 1), [Measures].[Num Entrate])))In this way if i select a Year a 2 Months , I can sum all months of previuos year.Otherwise PARALLELPERIOD give an error ...I hope it can be useful for othhers people...You can use that function also with this example in Adventure Works Cube..Here is an example...WITH MEMBER [Measures].[Test] AS SUM(Existing [Date].[Calendar].[Month].Members, ([Measures].[Order Count] - (ParallelPeriod([Date].[Calendar].[Calendar Year], 1), [Measures].[Order Count]))) SELECT {[Measures].[Test], [Measures].[Order Count]} on columns FROM [Adventure Works] WHERE ({[Date].[Calendar].[Month].&[2004]&[1], [Date].[Calendar].[Month].&[2004]&[2]}) Jonathan Mallia Ten Centuries Group: General Forum Members Points: 1223 Visits: 1290 Glad you finally solved it, and thanks for getting back with the solution.I think it works right for you if you go down to the date level of the hierarchy, correct?`WITH MEMBER [Measures].[Test] ASSUM( EXISTING [Date].[Calendar].[Date].Members, ( [Measures].[Order COUNT] - (ParallelPeriod([DATE].[Calendar].[Calendar YEAR], 1), [Measures].[Order COUNT]) ))SELECT {[Measures].[Test], [Measures].[Order COUNT]} ON COLUMNS, [Date].[Calendar].members ON ROWSFROM [Adventure Works]` maretix Old Hand Group: General Forum Members Points: 303 Visits: 234 Hi thanks for your reply.As soon as possibile I will test your post...'My' expression work if i do a MULTI SELECT with YEAR or MONTH Level.If i select DAY Level , i have to change my expression creating a new Measure...I will try also yours.Many thanks:-) regards. Jonathan Mallia Ten Centuries Group: General Forum Members Points: 1223 Visits: 1290 OK, let us know maretix Old Hand Group: General Forum Members Points: 303 Visits: 234 Hi.My Time dimension is like this :Dimension name => CalendarHierarchy => DataAAAAMMGGLevel 1 => YearLevel 2 => MonthLevel 3 => DayIf i use that expression you posted , how can i have to write that ???I tried but i receive error , i think i did not understand good ...sorry :-)Regards. Jonathan Mallia Ten Centuries Group: General Forum Members Points: 1223 Visits: 1290 I think your should be something like....`WITH MEMBER [Measures].[Test] ASSUM( EXISTING [Calendario].[DataAAAAMMGG].[Date].Members, ( [Measures].[Order COUNT] - (ParallelPeriod([Calendario].[DataAAAAMMGG].[AnnoVendita], 1), [Measures].[Order COUNT]) ))SELECT {[Measures].[Test], [Measures].[Order COUNT]} ON COLUMNS, [Calendario].[DataAAAAMMGG].members ON ROWSFROM [Adventure Works]`Check the [Calendario].[DataAAAAMMGG].[Date] member name though maretix Old Hand Group: General Forum Members Points: 303 Visits: 234 Hi many thanks for your reply.I will test as soon as possibile...I am thinking that in my Dimension i do not have a level name Date...Do you think i need to add in my dimension a level with this name ???Existing need a LEVEL not a HIERARCHY ...i WILL try thanks.