MDX:-Total of last N Days from Today.

Since I dont have forums on this blog.I would like to post the problems by mail using blogs.
Recently I got a problem to get sum of last N days from current Date.



I am posting a sample here which will give a sum of last 14 days from today.
We need to use VBA function to generated a string which is exactly like member name and then convert it to member using strtomember function.


This sample is using Adventureworks Database ,Analysis Service 2005.

with member [Measures].[CurrDate] as
"[Date].[Calendar].[Date].[" +Format(Now(),"MMMM") + " " + CStr(Day(Now()))+", "+CStr(Year(Now())) +"]"
set [Last14Days] as
LastPeriods (14, strtomember([Measures].[CurrDate]))
member [Date].[Calendar].[Total14Days]as
aggregate([Last14Days])
select [Measures].[Internet Sales Amount] on 0,
{[Last14Days],[Total14Days]} on 1
from
[Adventure Works]