CODEWITHSUNDEEP
ssmsmdxcubeolap-cubessms-2014
Nick Z
Nick Z

Reputation: 25

MDX - Dynamic query for last complete month

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!!

Upvotes: 0

Views: 1174

Answers (1)

Tom Huang
Tom Huang

Reputation: 192

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

Upvotes: 1

Related Questions