Another MDX question - about LastPeriod

Discussion in 'Analysis Services/Data Warehousing' started by Joozh, Feb 28, 2005.

1. JoozhNew Member

Hi All,<br /><br />sometime back I had posted a query regarding how to get the total of a measure for the last n periods... Raulie came to the rescue and the following solution worked:<br /><br /> <b>AVG(LastPeriods (3, [Time].CurrentMember), [Measures].[NetSales])</b><br /><br />I have been trying to use the above as the basis for a new requirement but no luck. Can someone please help me. Here's the scenario:<br /><br />For any level in the time Dimension, I want Average of the sales of the last three months. Now the LastPeriods function includes the selected month in the calculation and <b>so if the selected month is say December</b>, the solution mentioned above, will give the Sales of Oct, Nov, and Dec. <font size="4"><b>but</b></font id="size4"> I want the sales of Sep, Oct., and Nov instead of Oct, Nov, and Dec.<br /><br />I have tried the following but am getting a -ve results [?][?][:0][:0]<br /><br />AVG(LastPeriods (4, [Time].CurrentMember), [Measures].[NetSales]) - AVG(LastPeriods (1, [Time].CurrentMember), [Measures].[NetSales])<br /><br />Can someone please help me. Honestly when it especially comes to MDX, I feel like I am probably the person with the thickest skull [<img src='/community/emoticons/emotion-6.gif' alt='' />][xx(][V]<br /><br />Will appreciate your help.<br /><br />Regards.<br /><br />
2. VaniaNew Member

Please, try this:

AVG(LastPeriods (3, [Time].CurrentMember.PrevMember), [Measures].[NetSales])

It should work...
Bye,
Vania

3. JoozhNew Member

Tons of thanks Vania. This works.

Can you please guide me to some good resource/book/URL from where I can get a good understanding of MDX. I will appreciate your reply.

Regards.
4. VaniaNew Member

I suggest you to buy: "Mdx solutions", George Spofford - Wiley.
I find it very complete and reliable!
Bye.

5. JoozhNew Member

Many thanks (again) Vania.