CODEWITHSUNDEEP
sqlssasmdxcube
user3652818
user3652818

Reputation: 23

SSAS MDX Rolling Total Different Date Ranges

I am working on a finance cube with a calculated member using the following MDX formula:

IIF
 (
  [Chart Of Accounts].[Account Sub Group].CurrentMember 
   = 
  [Chart Of Accounts].[Account Sub Group].&[RESULT-CURRENT YEAR], 
  [Measures].[Amount GL YTD], 
  SUM
     (
      ParallelPeriod
                    (
                     [Date].[Year -  Quarter -  Month -  Date].[Year],
                     100,
                     [Date].[Year -  Quarter -  Month -  Date].CurrentMember
                    )
      :
      [Date].[Year -  Quarter -  Month -  Date].CurrentMember,
      [Measures].[Amount GL]
     )
 )

All works as expected. [RESULTS-CURRENT YEAR] displays the correct total for YTD while every other entry displays the totals from beginning of time. Except for the Equity rollup which [RESULTS-CURRENT YEAR] is in. It adds the totals from the beginning of time as well which is inflating the Total Equity on my pivot table.

Any ideas on how I can correct this?

Explanation Summary

Upvotes: 2

Views: 691

Answers (1)

whytheq
whytheq

Reputation: 35557

I don't understand the question but one thing I notice is that you have used = but to signal equality of members in mdx you should use IS

IIF
 (
  [Chart Of Accounts].[Account Sub Group].CurrentMember 
   IS
  [Chart Of Accounts].[Account Sub Group].&[RESULT-CURRENT YEAR], 
  [Measures].[Amount GL YTD], 
  SUM
     (
      ParallelPeriod
                    (
                     [Date].[Year -  Quarter -  Month -  Date].[Year],
                     100,
                     [Date].[Year -  Quarter -  Month -  Date].CurrentMember
                    )
      :
      [Date].[Year -  Quarter -  Month -  Date].CurrentMember,
      [Measures].[Amount GL]
     )
 )

To track down what is happening try adding these measures into your cube and then put them in the columns next to the troublesome column. What values do they return?.I suspect it might not return what you expect:

[Measures].[exploratory] AS
    [Date].[Year -  Quarter -  Month -  Date].CurrentMember.member_caption
[Measures].[exploratory2] AS
    [Chart Of Accounts].[Account Sub Group].CurrentMember.member_caption

Also I'm wondering if rather than going back 100 years maybe you could simplify by using null:

IIF
 (
  [Chart Of Accounts].[Account Sub Group].CurrentMember 
   IS
  [Chart Of Accounts].[Account Sub Group].&[RESULT-CURRENT YEAR], 
  [Measures].[Amount GL YTD], 
  SUM
     (
      NULL:[Date].[Year -  Quarter -  Month -  Date].CurrentMember,
      [Measures].[Amount GL]
     )
 )

Edit

I suspect that on the Grand Total row the currentmember of the hierarchy [Chart Of Accounts].[Account Sub Group] is the [All] member so the boolean condition of the iif is false. You could try amending to this:

IIF
 (
  (
   [Chart Of Accounts].[Account Sub Group].CurrentMember 
   IS [Chart Of Accounts].[Account Sub Group].&[RESULT-CURRENT YEAR]
  )
  OR
  (
   [Chart Of Accounts].[Account Sub Group].CurrentMember 
   IS [Chart Of Accounts].[Account Sub Group].[All]
  ) 
  ,[Measures].[Amount GL YTD] 
  ,SUM
     (
      NULL:[Date].[Year -  Quarter -  Month -  Date].CurrentMember,
      [Measures].[Amount GL]
     )
 )

Upvotes: 2

Related Questions