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:


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.

Advertisements

How to set SSRS date parameter default value to previous day?

Standard

Setting the default value for parameters like today’s date & current month is great because that way consumer do not need to manually input the parameter value. I was working on a requirement to pass previous day to the date parameter.

So here’s the quick tip to set the default value of the date parameter to previous day.

=Dateadd("d",-1,Now())

Screenshot:
SSRS date time parameter previous day