Calculating A % Rate Based On Two Date Dimension In One Cube

Set up - SSAS 2012 with OLAP cubes (built by supplier) and MS Report Builder v3. No access to BIDS. I am building a report which needs to calculate a disposal rate based on data f

Solution 1:

You can use LinkMember to move a reference to one hierarchy (like [Date Reported].[Calendar Months]) to another one (like [Disposal Date].[Calendar Months]), provided both hierarchies have the exact same structure. Thus, only using [Date Reported] in your query, the calculation can use [Disposal Date]. The query would be like the following:

WITHMEMBERMeasures.[DisposedinDateReported] AS
            (Measures.[NoofItems],
             LinkMember([DateReported].[CalendarMonths].CurrentMember, [DisposalDate].[CalendarMonths]),
             [DateReported].[CalendarMonths].[All]
            )
     MEMBERMeasures.[DisposalRate] ASIIf([Measures].[NoofItems] <> 0,
                Measures.[DisposedinDateReported] / [Measures].[NoofItems],
                NULL
               ), FORMAT_STRING = '0%'SELECT { [Measures].[NoofItems], Measures.[DisposedinDateReported], Measures.[DisposalRate] }
       ONCOLUMNS,
       [DateReported].[CalendarMonths].[Month].ALLMEMBERSONROWSFROM [Items]

Possibly, you would want to adapt the column titles in your report. I left that out and used member names that desribe more what they do than what should be shown to users.