# MDX on last 6 month

• ### Question

• I want to return list of most recent 6 month, so I need to find out what the current month is, then return list of 6 month back,for example, if current month is Nov, then I want to return Jun,Jul,Aug,Sept,Oct,Nov, so I try the following test using AdventureWorks:

select

{} on 0,

([Date].[Month of Year].

currentmember.lag(5): [Date].[Month of Year].currentmember) on 1

from

But it returns All Periods, instead of last 6 month, anything I miss?

Thanks

Don

Wednesday, November 11, 2009 5:25 AM

• Note that .currentmember will not automatically return the current month, unless the default member has been somehow set to it. There's a sample Adventure Works query below which works - you can refer to this blog entry for discussion of how to set the current date:

How to get the today's date in MDX

```With
Set [CurrentMonth] as
{[Date].[Calendar].[Month].&[2003]&[11]}
select{} on 0,
LastPeriods(6, [CurrentMonth].item(0)) on 1
from

- Deepak
• Proposed as answer by Friday, November 13, 2009 6:11 AM
• Marked as answer by Friday, November 20, 2009 9:18 AM
Wednesday, November 11, 2009 7:12 AM

### All replies

• I want to create mdx expression like "Running sales amount by most recent 3 month", so if it is now November, then the running total by most recent 3 month would be:
Amount
Sept    21211
Oct     12121
Nov     12122

What is the mdx function to do that? I know there is Lag function, but it seems Lag (3) of current member will give you Oct, Nov and Dec, which is not running 3 month starting from Nov.

Don
• Merged by Friday, November 20, 2009 9:17 AM the same topic
Tuesday, November 10, 2009 6:05 AM
• Hi,

Try this...

WITH

SET [Last 3 Months] AS
{Ancestor(Time.Time.CurrentMember,[Time].[Time].Month).Lag(2)
:Ancestor(Time.Time.CurrentMember,[Time].[Time].Month)}

SELECT
{[Last 3 Months]} on columns

from [Cube Name]

Regards
Gokul

Tuesday, November 10, 2009 6:26 AM
• Using  LastPeriods works perfectly in your case. Here is the complete MDX that I got it working in Adventure Works 2008 database.

WITH
MEMBER [Measures].[Last3Months] AS
Sum
(
{
LastPeriods
(3
,[Date].[Fiscal].CurrentMember
)
}
,[Measures].[Amount]
)
SELECT
{
[Measures].[Last3Months]
,[Measures].[Amount]
} ON COLUMNS
,Descendants
(
[Date].[Fiscal].[Fiscal Year].&[2004]
,[Date].[Fiscal].[Month]
,self
) ON ROWS

Thanks,
Ashok
Tuesday, November 10, 2009 10:45 AM
• Note that .currentmember will not automatically return the current month, unless the default member has been somehow set to it. There's a sample Adventure Works query below which works - you can refer to this blog entry for discussion of how to set the current date:

How to get the today's date in MDX

```With
Set [CurrentMonth] as
{[Date].[Calendar].[Month].&[2003]&[11]}
select{} on 0,
LastPeriods(6, [CurrentMonth].item(0)) on 1
from

- Deepak
• Proposed as answer by Friday, November 13, 2009 6:11 AM
• Marked as answer by Friday, November 20, 2009 9:18 AM
Wednesday, November 11, 2009 7:12 AM
• Hi

Definetely you got your result set, but i prefer that right here this link more helpfull to others and also for those who are new with MDX.

Please check the below link in which date filtering describes with different taste for reducing the updating time, and how beautifully describe about the AS2008 boclk system.

http://sqlblog.com/blogs/mosha/archive/2008/10/22/optimizing-mdx-aggregation-functions.aspx

Thanks
M Ahsan

Tuesday, April 2, 2013 5:34 AM