Finding Avg on multiple Date Dimension Hierarchies

Question

I have a cube, and within that cube there is a date dimension which has the following attributes

Calendar Year, Calendar Month-Year, Fiscal Year, Fiscal Year-Quarter, Date

Now with these attributes I have two user defined hierarchies, they are as follows Calendar Hierarchy which has Calendar Year, Calendar Month-Year, Date

The second user defined hierarchy is Fiscal Hierarchy which has Fiscal Year, Fiscal Year-Quarter, Calendar Month Year and Date

I want to know the average sales so I created a measure which goes

Member [Measures].[Sales Daily Avg]
AS Avg ( Descendants ( [Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Date] ), CoalesceEmpty([Measures].[Total Sales] , 0) )

This works great when running something like

SELECT {
[Measures].[Sales Daily Avg],
} ON COLUMNS,
{ [Date].[Calendar].[Calendar Year].Members } ON ROWS
FROM 
[SalesCube]

This will work if I use [Date].[Calendar].[Calendar Month-Year].Members but it will not work on my fiscal hierarchy attributes except for Calendar Month-Year. How can I modify my measure so that it will accept the fiscal year hierarchy attributes.

I tried cross joining by the fiscal year, because if the set is empty, it will just evaluate the one that is being used. But it just doesnt work for me ( I am assuming sytnax might be the issue).

Sorry guys, I am really new at this.

SouravA SouravA · Accepted Answer · 2015-10-30T09:28:34

Change the definition of calculated member to :

CREATE Member [Measures].[Sales Daily Avg]
AS 
IIF
  (
   NOT([Date].[Fiscal].CURRENTMEMBER IS [Date].[Fiscal].[All])
   ,
   Avg 
     ( 
      Descendants 
                (
                 [Date].[Fiscal].CURRENTMEMBER, 
                 [Date].[Fiscal].[Date] 
                )
     ,
     CoalesceEmpty([Measures].[Total Sales] , 0) 
    )
   ,
   IIF
     (
      NOT([Date].[Calendar].CURRENTMEMBER IS [Date].[Calendar].[All])
      ,
      Avg 
        ( 
         Descendants 
                (
                 [Date].[Calendar].CURRENTMEMBER, 
                 [Date].[Calendar].[Date] 
                )
        ,
        CoalesceEmpty([Measures].[Total Sales] , 0)
        ),
        NULL //Change this to whatever you want in case none of the hierarchies 'Calendar' or 'Fiscal' is involved.
     )
     )

Basically this will first check if any member from "Fiscal" hierachy is in scope or not. If so, it will average out on the Fiscal dates for the selected member. If the first condition is not met, it further checks if any member from "Calendar" hierarchy is in scope. If so, it works similarly over "Calendar" hierarchy. Finally if none of the hierarchy's members are in scope, it yields NULL(you might rethink and put as per your requirement).

NOTE: when the currentmember of any hierarchy is the All member, that means that hierarchy is not in scope(not part of slicer or the axes)

Finding Avg on multiple Date Dimension Hierarchies

1 Answers