SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: How to create calculated member for AVG sales over last 3 years based on NOW()?

A: If your last member in date dimension does not represent last loaded day, then you will have to use NOW() to get Current date. Example of such query depends on your date format. You can use date day key or name to generate member using format function.

Lets say your Day Key has format YYYYMMDD, then this will give you current day member:

StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")

So average 3 last years (AVG([Date 3 year ago]: [Todays Date], Measure)

CREATE MEMBER CurrentCube.Measures.[Avg3Years] AS
Avg(
{ParallelPeriod( [Date].[Date].[Date Yr], 3, StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]"))
: StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")}
, [Measures].[Sales Qty]
) ;