Technical: Microsoft – SQL Server – Analysis Services – MDX – Function – LastPeriods

 

Technical: Microsoft – SQL Server – Analysis Services – MDX – Function – LastPeriods

 

Background

Needing to do some Date Arithmetic with MDX.  The basic premise being the need to fetch data for N days relative to the current date.

 

 

Getting Data for last 7 days using LastPeriods



/*
   Example:
	   https://sqlserverbiblog.wordpress.com/tag/mdx-queries/
*/
select 
		{
			[Measures].[Total Due]
		} on columns

	, NON EMPTY LASTPERIODS
		( 
			7
			, [Time].[Date As YYYYMMDD].&[20140406] 
		) on rows


from [MultidimensionalProject_Time]



 

Screenshot:

NonEmptyLastPeriods

 

 

Getting Data for current year using LastPeriods

In the sample below, we create a couple of Calculated members (TodayDate, DateYYYYMMDD as an Integer value of YearMonthDay, and Day of Year as DayofYear).

 



WITH  
MEMBER [Measures].[TodayDate] AS NOW()
MEMBER [Measures].[DateYYYYMMDD] AS format(NOW(), "yyyyMMdd")
MEMBER [Measures].[DayofYear] AS StrToValue(datepart("y",Now()),CONSTRAINED)
select 
          NON EMPTY 
		(
			{
				 ([Measures].[Total Due])
      	                    -- ,([Measures].[DateYYYYMMDD]) 
     	      	            -- ,([Measures].[DayofYear]) 
			}

		) on columns

	, NON EMPTY LASTPERIODS
		( 
			  [Measures].[DayofYear]
			, StrToMember(
					"[Time].[Date As YYYYMMDD].&["
					+ [Measures].[DateYYYYMMDD]
            	                        +"]"		
				    )
		) on rows


from [MultidimensionalProject_Time]


 

Screenshot:

NonEmptyLastPeriodsForCurrentYear

 

 

Listening

Listening to:

Ed Sheeran – The A Team [Official Music Video] https://www.youtube.com/watch?v=UAWcs5H-qgQ

 

References

References – Date Sample

 

References – MDX – Function – LastPeriod

 

References – MDX – Function – StrToValue

 

References – Date Function References

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s