Annualised Revenue - SUM of revenue for last 12 complete months SSAS mdx calculation

Question

I'm new to MDX world and currently enhancing ssas cube.

I’m trying to create calculated member in SSAS cube to get Annualised Revenue(SUM of revenue for last 12 complete months) for each of device(product). As a start created below MDX query, but its showing only device names, Annualised Revenue shows (null).

Any correction needed for this MDX Query? or appreciate, if someone can give me an example based on AdventureWorks cube.

Here is my MDX query:

WITH 
  MEMBER [Measures].[Annualised Revenue] AS 
    Sum
    (
        ClosingPeriod
        (
          [Invoice Date].[Calendar Month].[Invoice Calendar Month]
         ,[Invoice Date].[Calendar Month].[All Periods]
        ).Lag(12)
      : 
        ClosingPeriod
        (
          [Invoice Date].[Calendar Month].[Invoice Calendar Month]
         ,[Invoice Date].[Calendar Month].[All Periods]
        )
     ,[Measures].[Amount]
    ) 
SELECT 
  [Measures].[Annualised Revenue] ON 0
 ,[Terminal].[Terminal ID].MEMBERS ON 1
FROM [cube_txn];

Do i need to add where cluase as “where ( [Invoice Date].[Calendar Month].[Invoice Calendar Year].&[2013])”

Whenerver it runs it should pickup current month as base and calculate for last 12 months revune

I am expecting results as below(ignore jan14 & Jan15):

deviceID    SumOfAnnualisedRevenue  Jan14   Jan15
---------   ----------------------  -----    ------
A10001      12,4500     5000   15000
A10003      45,50000    15000   78000
A10006      78,00       12000   890
A10008      8,945       450     120

whytheq whytheq · Accepted Answer · 2015-03-17T15:05:01

Does this help?

WITH 
  MEMBER [Measures].[Annualised Revenue] AS 
    Sum
    (
        ClosingPeriod
        (
          [Invoice Date].[Calendar Month].[Invoice Calendar Month]
         ,[Invoice Date].[Calendar Month].[All Periods]
        ).Lag(12).item(0).item(0)
      : 
        ClosingPeriod
        (
          [Invoice Date].[Calendar Month].[Invoice Calendar Month]
         ,[Invoice Date].[Calendar Month].[All Periods]
        ).item(0).item(0)
     ,[Measures].[Amount]
    ) 
SELECT 
  [Measures].[Annualised Revenue] ON 0
 ,[Terminal].[Terminal ID].MEMBERS ON 1
FROM [cube_txn];

If this is erroring [Terminal].[Terminal ID].MEMBERS then try one of the following instead (I cannot say for sure which will work without seeing your db):

[Terminal].[Terminal ID].[Terminal ID].MEMBERS 
[Terminal].MEMBERS
[Terminal ID].[Terminal ID].MEMBERS

Annualised Revenue - SUM of revenue for last 12 complete months SSAS mdx calculation

1 Answers