How can I write an MDX statement or query that selects the clients who paid last month but have not paid this(Current Month) month?.

Question

How can I write an MDX statement or query that selects the clients who paid last month but have not paid this(Current Month) month?. I have a data cube designed and deployed on Microsoft SQL Server Analysis Services R 2. I have a customer dimensions and a fact table. Please help.

Answer 1

I assume you have a [Time] dimension, and a measure that contains a value if the client has paid. I think the Filter() function will help you reduce a set of all clients down to just the clients that you're interested in.

SELECT {Filter({[Client].[SomeLevel].members}, ([Time].[LastMonth], [Measures].[whatever]) > 0 AND ([Time].[ThisMonth], [Measures].[whatever]) = 0} ON ROWS, {[Measures].[whatever]} ON COLUMNS FROM [CubeName]

You trouble might be deciding what to use in place of where I wrote [Time].[ThisMonth] - see other answers here on StackOverflow for selecting 'current' dates.

Answer 2

First of all, you have to identify the current month and the last month. One way to do it is compute it using the VBA!Date function.

So if your dates are stored in the format 12/31/2014 and assuming you have a measure Payment and a Date dimension with a Year-Quarter-Month-Date hierarchy, the below code can help you.

WITH MEMBER [Measures].ValueThisMonth AS
(
 [Date].[Year-Quarter-Month-Date].CURRENTMEMBER.PARENT, 
 [Measures].[Payment]
)

MEMBER [Measures].ValueLastMonth AS
(
 [Date].[Year-Quarter-Month-Date].CURRENTMEMBER.PARENT.LAG(1), 
 [Measures].[Payment]
)

SELECT [Client].[Client Name].MEMBERS
HAVING ISEMPTY([Measures].ValueThisMonth)
AND NOT(ISEMPTY([Measures].ValueLastMonth))
ON 0
FROM [Your cube]
WHERE 
StrToMember("[Date].[Year-Quarter-Month-Date].[Date].&[" + FORMAT(VBA![Date](), "MM/dd/yyyy") + "]" + "]")

If instead you would like to pass this "current" value from the front end, use a parameter in the WHERE clause.