SQL Server Performance

Another MDX question - about LastPeriod

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

  1. Joozh New 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. Vania New Member

    Please, try this:

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

    It should work...

  3. Joozh New 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.

  4. Vania New Member

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

  5. Joozh New Member

    Many thanks (again) Vania.

Share This Page