0
votes

I want to create a calculated measure in SSAS using an MDX query. This measure should return the average amount within a period of a year and the same period last year. I've found out about the ParallelPeriod function but couldn't manage to use it.

My query should look like this

(
 AGGREGATE(
   {[TF_Product].[LastDayOfMonth]}
   *{[TF_Product].[Category].&[Deposits]},
   [Measures].[Montant]
 )
 +
 AGGREGATE(
   { Get the date of the same period last year using parallelperiod}
   *{[TF_Product].[Category].&[Deposits]},
   [Measures].[Montant]
 )
)/2

Screenshot

Thank you.

2
The Daily average? So (1jan-4Apr2015 + 1jan-4Apr2016)/numdays ?whytheq
I want to calculate for example : (amount in January 2016 + amount in January 2015) / 2yasmine92

2 Answers

0
votes

Does something like this work?

...
MEMBER [Measures].[Avg of 2 Mths] AS
  Avg(
    {[Date].[Month].CurrentMember.Lag(12),[Date].[Month].CurrentMember}
    [Measures].[Sales Quantity]
  )
...

It works in this context:

WITH 
  MEMBER [Measures].[Avg of 2 Mths] AS 
    Avg
    (
      {
        [Date].[Calendar].CurrentMember.Lag(12)
       ,[Date].[Calendar].CurrentMember
      }
,[Measures].[Internet Sales Amount]  
  ) 
SELECT 
  {[Measures].[Internet Sales Amount],[Measures].[Avg of 2 Mths]} ON 0
 ,[Date].[Calendar].[Month] ON 1
FROM [Adventure Works];

This is the result:

enter image description here

(755527.89 + 577314)/ 2 = 666420.95

0
votes

This works?

with member [Measures].AvgAmnt as
(
 AGGREGATE(
   {[TF_Product].[Calendar].Currentmember}
   *{[TF_Product].[Category].Currentmember},
   [Measures].[Montant]
 )
 +
 AGGREGATE(
   {ParallelPeriod([TF_Product].[Calendar].[Month],1,[TF_Product].[Calendar].Currentmember)}
   *{[TF_Product].[Category].Currentmember},
   [Measures].[Montant]
 )
)/2

select [TF_Product].[Calendar].[Date].members * [TF_Product].[Category].members on 1,
[Measures].AvgAmnt on 0
from [SomeCube]

Have assumed you have a Date hierarchy(Calendar) in place in your cube.