Working on an MDX query in SSMS 2014, and I need it to be dynamic in that it always queries data for the last complete month.
Right now I am using the Lag()
function to determine the last month and the month prior to that in order to get a month-over-month calculation. And I am specifying the current month with {[Date].[Calendar Year Month].[201705]}
in order to pull that data.
Is there anyway I can avoid specifying the month so that I don't have to edit the query each time I run it?
Here is my current query:
WITH
MEMBER [2MonthsPriorUniqueUsers] AS Sum({[Date].[Calendar Year Month].CurrentMember.Lag(2)},[Measures].[App Unique Users Loggedin])
MEMBER [1MonthPriorUniqueUsers] AS Sum({[Date].[Calendar Year Month].CurrentMember.Lag(1)},[Measures].[App Unique Users Loggedin])
MEMBER [1MonthPriorTotalAppLogins] AS Sum({[Date].[Calendar Year Month].CurrentMember.Lag(1)},[Measures].[App Logins])
MEMBER [Measures].[PercentUniqueIncreaseMoM] AS ([1MonthPriorUniqueUsers] / [2MonthsPriorUniqueUsers])-1,FORMAT_STRING = "Percent"
SELECT
NON EMPTY
{[Date].[Calendar Year Month].[201705]}
* Nonempty([Project].[Client Name].members,[Measures].[App Logins]) ON 0,
NON EMPTY
{
[Measures].[PercentUniqueIncreaseMoM]
,([1MonthPriorUniqueUsers])
,[2MonthsPriorUniqueUsers]
,[1MonthPriorTotalAppLogins]
} ON 1
FROM [MyCube]
Thanks in advance!!
I think you can just replace your code below:
[Date].[Calendar Year Month].[201705]
to
StrToMember("[Date].[Calendar Year Month].[" + Format(Now(),"yyyyMM") + "]")
www.mdx-helper.com