MDX Calculated Members by Month

Question

I'm working on a query in SSMS 2014 where I need [Measures].[Percent Unique App Users] by month, so that I may dynamically calculate the percent increase month over month. I'd preferably like to start with comparing last month to the month prior, and this will eventually end up being run as a report every month (hence the need for it to be dynamic).

With or without my DATE Members, the count of [CurrentMonthUniqueUsers] and [LastMonthUniqueUsers] stay the same (show in example). Additionally, without my WHERE statement, I only get results for my non-MEMBER rows.

WITH
MEMBER [LastMonthUniqueUsers] as SUM({[Date].[Calendar].CurrentMember.Lag(2)},[Measures].[App Unique Users Loggedin])
MEMBER [CurrentMonthUniqueUsers] as SUM({[Date].[Calendar].CurrentMember.Lag(1)},[Measures].[App Unique Users Loggedin])
//
MEMBER [Measures].[Percent Unique App Users] AS ([CurrentMonthUniqueUsers]) / ([LastMonthUniqueUsers]), FORMAT_STRING = "Percent"
//
MEMBER [CurrentDate] As Now()
//

SELECT
    non empty[Project].[Client Name].children ON 0,
    non empty{[Measures].[Percent Unique App Users],
        [CurrentMonthUniqueUsers],
        [LastMonthUniqueUsers], 
        [Measures].[App Logins],
        [Measures].[App Unique Users Loggedin]} ON 1
FROM
    [OpsViewWarehouse]
WHERE
    [Date].[Year].[2017]

The image below is what I am getting for results. The login counts and unique login counts are for all time and are not getting filtered by any date.

Data Screenshot

I'm new to MDX, so any help would be greatly appreciated!

EDIT (5/22/2017): Here is the query that worked with the answer I was provided by whytheq. Note that it isn't yet dynamic in that I have to specify a date (201705) on the column.

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]}
   *  [Project].[Client Name].members ON 0,
  NON EMPTY 
    {
      [Measures].[PercentUniqueIncreaseMoM]
     ,[1MonthPriorUniqueUsers]
     ,[2MonthsPriorUniqueUsers]
     ,[1MonthPriorTotalAppLogins]
    } ON 1
FROM [OpsViewWarehouse]

[Date].[Year].[2017] -> [Date].[Calendar].[2017] may solve a problem. — Danylo Korostil

whytheq whytheq · Accepted Answer · 2017-05-19T08:05:17

In your query your CURRENTMEMBER is what is in the WHERE clause - which is 2017 - so this won't be returning a month - it's probably returning the member [Date].[Calendar].[All]:

[Date].[Calendar].CurrentMember

Try adding months into either the SELECT or WHERE clause:

//
WITH 
  MEMBER [LastMonthUniqueUsers] AS 
    Sum
    (
      {[Date].[Calendar].CurrentMember.Lag(2)}
     ,[Measures].[App Unique Users Loggedin]
    ) 
  MEMBER [CurrentMonthUniqueUsers] AS 
    Sum
    (
      {[Date].[Calendar].CurrentMember.Lag(1)}
     ,[Measures].[App Unique Users Loggedin]
    ) 
  //
  MEMBER [Measures].[Percent Unique App Users] AS 
    [CurrentMonthUniqueUsers] / [LastMonthUniqueUsers] 
   ,FORMAT_STRING = "Percent" 
  //
  MEMBER [CurrentDate] AS 
    Now() 
SELECT 
  NON EMPTY 
      [Project].[Client Name].Children ON 0
 ,NON EMPTY 
    {[Date].[Calendar].[Month].&[Jan 2017]}  //<<<<<<change to a month member in your cube
  * {
      [Measures].[Percent Unique App Users]
     ,[CurrentMonthUniqueUsers]
     ,[LastMonthUniqueUsers]
     ,[Measures].[App Logins]
     ,[Measures].[App Unique Users Loggedin]
    } ON 1
FROM [OpsViewWarehouse];

The above could also be a set of months:

//
WITH 
  MEMBER [LastMonthUniqueUsers] AS 
    Sum
    (
      {[Date].[Calendar].CurrentMember.Lag(2)}
     ,[Measures].[App Unique Users Loggedin]
    ) 
  MEMBER [CurrentMonthUniqueUsers] AS 
    Sum
    (
      {[Date].[Calendar].CurrentMember.Lag(1)}
     ,[Measures].[App Unique Users Loggedin]
    ) 
  //
  MEMBER [Measures].[Percent Unique App Users] AS 
    [CurrentMonthUniqueUsers] / [LastMonthUniqueUsers] 
   ,FORMAT_STRING = "Percent" 
  //
  MEMBER [CurrentDate] AS 
    Now() 
SELECT 
  NON EMPTY 
    {
      [Measures].[Percent Unique App Users]
     ,[CurrentMonthUniqueUsers]
     ,[LastMonthUniqueUsers]
     ,[Measures].[App Logins]
     ,[Measures].[App Unique Users Loggedin]
    } ON 0
 ,NON EMPTY 
     {Date].[Calendar].[Month].MEMBERS  //<<<<<<change to the month hierarchy in your cube
   *  [Project].[Client Name].Children ON 1
FROM [OpsViewWarehouse];

MDX Calculated Members by Month

1 Answers