SQL Server Performance Forum – Threads Archive
Another MDX question – about LastPeriod
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 />
Please, try this: AVG(LastPeriods (3, [Time].CurrentMember.PrevMember), [Measures].[NetSales]) It should work…
Bye,
Vania
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.
I suggest you to buy: "Mdx solutions", George Spofford – Wiley.
I find it very complete and reliable!
Bye.
Many thanks (again) Vania.
]]>