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

Standard

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:

[code language=”sql” gator=”false”]

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]

[/code]

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.

What do you think? Leave a comment below.