title: SSAS -> KPI Creation -> How to create a kpi that compares the rank of a customer on one day compared to the previous day?
body: <p>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.  </p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>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] 
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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].&amp;[2012-01-08T00:00:00]}
  </code></pre>