SSAS -> KPI Creation -> How to create a kpi that compares the rank of a customer on one day compared to the previous day?

Question

I have been working on creating a kpi that compares the rank of a customer on a specific date compared to the previous day. In turn you would be able to see if that customer has went up in rank or down in rank in terms of whatever the list was generated by. In my situation they are ordered by revenue.

I am able to rank my customers via the rank function easily enough and provide the report but when it comes to creating the kpi of comparing these ranks across days I am struggling in figuring out how this should be approached. The rank itself is not something stored as data it is something that I will need to create on the fly via the rank function.

Here is an example of my mdx query that I am using to create my initial starting report that provides me with rank of customers without a date splice:

WITH SET [RevRank] AS
ORDER (
    [Customer].[Customer Id].CHILDREN ,
    [Measures].[Revenue], BDESC)  
  MEMBER [Measures].[RANKRevenue] AS RANK([Customer].[Customer Id].CurrentMember, [RevRank] )

SELECT NON EMPTY { [Measures].[Revenue], [Measures].[Fact Order Count], [Measures].[RANKRevenue] } ON COLUMNS, 
  NON EMPTY TopCount( { ([RevRank] ) } , 100, [Measures].[Revenue]) ON ROWS 
FROM [DW] 

From this I am attempting to splice in a specific date (day) and then compare that rank to a previous day within a kpi. So, starting off I am working on breaking this query up. I created a pre calculated set and pre calculated member to help me do this more easily. Now I am just trying to figure out how to create this set and member by a day and then I can at least produce a comparison between one day and the next.

01/26/2012 Update: Ok, I am a bit further down the road on this, but I am still having issues with getting the rank to pull into my query, the query below has nulls for the rankings. Hopefully someone can see the issue with this query.

WITH MEMBER  [Measures].[PrevDayRevenue] AS
( [Measures].[Revenue], ParallelPeriod ([Date Link].[PK Date].[PK Date],1))
SET [RevRankPrevOrder] AS
ORDER (
   [Customer].[Customer Id].Members ,
   [Measures].[PrevDayRevenue],
   BDESC)
MEMBER [Measures].[RANKRevenuePrevOrder] AS RANK([Customer].CurrentMember, [RevRankPrevOrder])
SET [RevRankCurrOrder] AS
ORDER (
   [Customer].[Customer Id].Members ,
   [Measures].[Revenue],
   BDESC)
MEMBER [Measures].[RANKRevenueCurrOrder] AS RANK([Customer].CurrentMember, [RevRankCurrOrder])

SELECT NON EMPTY { [Measures].[Revenue],  [Measures].[PrevDayRevenue], [Measures].[RANKRevenuePrevOrder], [Measures].[RANKRevenueCurrOrder] } ON COLUMNS,
NON EMPTY { ( [RevRankCurrOrder] ) } ON ROWS 
FROM [DW] 
WHERE {[Date Link].[PK Date].&[2012-01-08T00:00:00]}

Answer 1

You can use the ParallelPeriod function to calculate the rank for the prior day. This will also need to be done on the fly.

Then you can just compare the two in your KPI value...

CASE
    WHEN [Customer Rank Yesterday] - [Customer Rank Today] > 0 THEN 1
    WHEN [Customer Rank Yesterday] - [Customer Rank Today] = 0 THEN 0
    ELSE -1
END

Answer 2

Here is my finished query, hope this helps someone else. :

WITH MEMBER  [Measures].[PrevDayRevenue] AS
( [Measures].[Revenue], ParallelPeriod ([Date Link].[PK Date].[PK Date],1))

SET [RevRankPrevOrder] AS
ORDER (
   [Customer].[Customer Id].CHILDREN ,
   [Measures].[PrevDayRevenue],
   BDESC)

MEMBER [Measures].[RANKRevenuePrevOrder] AS 
    RANK(
    [Customer].[Customer Id].CurrentMember,
    [RevRankPrevOrder])

SET [RevRankCurrOrder] AS
ORDER (
   [Customer].[Customer Id].CHILDREN,
   [Measures].[Revenue],
   BDESC)

MEMBER [Measures].[RANKRevenueCurrOrder] AS RANK([Customer].[Customer Id].CurrentMember, [RevRankCurrOrder])

SELECT NON EMPTY { [Measures].[Revenue],  [Measures].[PrevDayRevenue], [Measures].[RANKRevenuePrevOrder], [Measures].[RANKRevenueCurrOrder]  } ON COLUMNS,
NON EMPTY { ( [RevRankCurrOrder] ) } ON ROWS 
FROM [DW] 
WHERE {[Date Link].[PK Date].&[2012-01-10T00:00:00]}