Calculated Measure Same Period LY Totals Incorrect

# Calculated Measure Same Period LY Totals Incorrect

• Friday, March 02, 2012 8:09 AM

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...

### All Replies

• Friday, March 02, 2012 9:29 AM

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

• Wednesday, March 07, 2012 5:33 AM
Moderator

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.

thanks,
Jerry

• Wednesday, March 07, 2012 6:20 AM

Hi Jerry,

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

• Tuesday, March 20, 2012 1:15 PM

I still haven't found a workable workaround.

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