Calculated Measure Same Period LY Totals Incorrect

Question

Hi,

We have a simple calculated measure to calculate the sales difference to the same period Last Year as a percentage.  This works in genereal except if you add a filter on a period say the month level in each year and then look at the data in a pivot table.  The totals above the levels you are looking at are then incorrect because the filter is ignored for higher levels and it looks at the parallel period for that level.  So in this example the total on year level would be the sales of the selected month TY compared to the sales for the full year LY.

Here are the calculated measures that we tried:

Create Member CurrentCube.[Measures].[Internet Sales Var % LY]
 AS 
 IIF(([Measures].[Internet Sales Amount],parallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember)) > 0
   ,([Measures].[Internet Sales Amount]/([Measures].[Internet Sales Amount],parallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember)) - 1) 
   ,0)
 , 
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior = [Internet Sales Amount]; 
  
Create Member CurrentCube.[Measures].[Internet Sales Var % LY1]
 AS 
IIF(([Measures].[Internet Sales Amount],parallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember)) > 0
  ,([Measures].[Internet Sales Amount]/
   SUM( EXISTING [Date].[Fiscal].[Month].Members,
        (
          ParallelPeriod([Date].[Fiscal].[Fiscal Year], 1, [Date].[Fiscal].currentmember ),
          [Measures].[Internet Sales Amount]
        )
      )
      ) - 1
,0)
 , 
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior = [Internet Sales Amount]; 

Correct on Year Level:

Totals incorrect on month level when filtered on July in both years:

This would seem like a common problem, but could not find a solution.

Any help would be appreciated...

Answer 1

Hi Kobus,

It seems that LY1 is defined at the correct needed granularity: the month level. In the way you define LY, it would only take the precalculated aggregates at the year level into account. 

Regards,
Philip

Answer 2

Hi Kobus,

In the 2nd picture you provide, it's very strange on that the internet sales amount are always same in any level of the year. So i suspect the granularity is not correct setted up for the Internet sale amount. To further investigate on this problem, please answer the following 2 questions -

1. What is the date granularity of the Internet Sales Amount? - why i ask this quesiton because the sales are always same at any level in one year.

2. Is the date dimension fully populated? that is, is there any date lost amount the date range of the date dimenion? - why i ask this question because some function like ParallelPeriod will do wrong date calculation if the date dimension is not fully populated.

Hope this helpfully,

thanks,
Jerry

Answer 3

Hi Jerry,

Thanks for your reply.  It looks like an Excel bug to me, because I get the expected behaviour when I browse the Cube in SSAS cube browser.  My users are giving me a hard time about this and I'm wondering if there is a workaround to let it work in Excel.  I get the same behaviour in my live cube, so I doubt that has something to do with the granularity and my date dimension is definitely fully populated.  In the example I used the Adventure Works DW 2008R2 Cube as I downloaded it and it connects to the AdventureWorksDW2008R2 sampel DB (no changes).  The only change I made to the cube was to add the calculated measure.

Here's the filter I use:

I've found that if you remove the filter on July 2007 the values for the Calculated Measure Internet Sales Var % LY stay the same, so Excel ignores the filter for the calculated measure.

It works in cube browser:

Here's the MDX that Excel genereates:

SELECT NON EMPTY
Hierarchize(
  DrilldownMember(
    {{DrilldownMember(
      {{DrilldownMember({{DrilldownLevel({[Date].[Fiscal].[All Periods]},,,INCLUDE_CALC_MEMBERS)}},
                         {[Date].[Fiscal].[Fiscal Year].&[2008],[Date].[Fiscal].[Fiscal Year].&[2009]},,,INCLUDE_CALC_MEMBERS
                       )}}, {[Date].[Fiscal].[Fiscal Semester].&[2008]&[1],[Date].[Fiscal].[Fiscal Semester].&[2009]&[1]},,,INCLUDE_CALC_MEMBERS
                     )}}, {[Date].[Fiscal].[Fiscal Quarter].&[2008]&[1],[Date].[Fiscal].[Fiscal Quarter].&[2009]&[1]},,,INCLUDE_CALC_MEMBERS
                  )
           ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,
             [Date].[Fiscal].[Fiscal Semester].[Fiscal Year],
             [Date].[Fiscal].[Fiscal Quarter].[Fiscal Quarter of Year],
             [Date].[Fiscal].[Fiscal Quarter].[Fiscal Semester],
             [Date].[Fiscal].[Fiscal Quarter].[Fiscal Semester of Year],
             [Date].[Fiscal].[Month].[Calendar Quarter],
             [Date].[Fiscal].[Month].[Month of Year] ON COLUMNS ,
  {[Measures].[Internet Sales Amount],
   [Measures].[Internet Sales Var % LY],
   [Measures].[Internet Sales Var % LY1]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS
FROM (SELECT ({[Date].[Fiscal].[Month].&[2008]&[7], [Date].[Fiscal].[Month].&[2007]&[7]}) ON COLUMNS
FROM [Adventure Works]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

It seems that because it is using the DrilldownMember to get where it wants to be, at that level the ParallelPeriod function jumps to the previous level and the filter is ignored.

I hope Microsoft can fix this.

Thanks, Kobus

Answer 4

I still haven't found a workable workaround.

I would forever be grateful if someone can think of something.