Create Dynamic Time Dimension Named Set in SSAS

This post will explain that How to create the Time Dimension Dynamic named set using CurrentDate .

Most of the people might have know about this. This post is for self reference and sharing the knowledge.

In My cube I have Calandar Time Hierarchy
My Hierarchy is
[Year] -> [Quarter] -> [Month] -> [Day]
Here i explained some Named Set.

1. [Last years]
2. [Last Quarter]
3. [Last Months]
4. [Last Days]

Step1: First Find the Time Dimension Hierarchy Member from your cube, and select the Hierarchy member inside your Query Window.
MemberName of Year,Quarter,Month,Day :
[Date].[Time].[Year].&[2009]
[Date].[Time].[Year].&[2009].&[2]
[Date].[Time].[Year].&[2009].&[2].&[5]
[Date].[Time].[Year].&[2009].&[2].&[5].&[15]

Now you will get this query.

Step2:
Frame the string member as it is in the step1. then create the Name set by using [New Named Set] in the cube Calculation Tab.

The following link will help you to derive DateParts from Current Date.
http://www.ssas-info.com/analysis-services-faq/27-mdx/78-how-write-mdx-query-that-uses-execution-datetime-now-as-parameter

1. [Last Years]

LASTPERIODS(3, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "]"))

2.[Last Quarter]

LASTPERIODS(4, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ cstr(datepart( "q", Now()))
+ "]"))

3. [Last Month]

LASTPERIODS(6, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ cstr(datepart( "q", Now()))
+ "].&["
+ Format(Now(), "MM")
+ "]"))

4.[Last Day]


LASTPERIODS(30, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ cstr(datepart( "q", Now()))
+ "].&["
+ Format(Now(), "MM")
+ "].&["
+ Format(Now(), "dd")
+ "]"))

here you can define the lastperiod as your wish.

** Any feedback about this Article are Welcome **