date

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.

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.

[code type=”sql” gutter=”false”]
=Dateadd("d",-1,Now())
[/code]

Screenshot:
SSRS date time parameter previous day