SQL Server Analysis Services MDX: Sum of Last N Years, Quarters, Months, Periods.

Problem Statement:

create a calculated measure in MDX to compute last 3 years Sales Amount

Solution:

Here’s the MDX code to calculate sum of sales amount for last three calendar years:


with member [measures].[Sales Amount Last Three Years]
as 
SUM({[Date].[Calendar].CurrentMember:[Date].[Calendar].CurrentMember.PrevMember.PrevMember},[Measures].[Sales Amount])
select 
[measures].[Sales Amount Last Three Years] on 0
from 
[Adventure Works]
where 
[Date].[Calendar].[Calendar Year].&[2008]

Results:

MDX Caculated Measure Sum of sales amout LAST N PERIODS

I hope that gives you a good starting point to calculate the Sum of Measure during Last N Periods.

One thought on “SQL Server Analysis Services MDX: Sum of Last N Years, Quarters, Months, Periods.

  1. Pingback: SQL Server Analysis Services MDX: How to display last/rolling 12 months trend in MDX? | Paras Doshi - Blog

Leave a Reply

Fill in your details below or click an icon to log in:

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