Setting an MDX goal expression for baseline average in select months

Question

Our data is updated monthly and the last date of available data can be anywhere from 45-75 days behind. For example, data is current through 6/30/2014 as of 8/25/2014.

I have a Time dimension hierarchy named [Calendar] with [Year]>[Half Year]>[Quarter]>[Month]>[Date] I have a measure, [Measures].[Fatalities] set as a calculated member. I have a KPI with [Measures].[Fatalities] set as the Value Expression. My issue is with setting up the goal expression.

I am trying to set a goal expression that is a percent reduction for a 5-year baseline average (Year-7:Year-3). So for the current year (2014, with data through June 2014), the goal would be a 20% reduction of the average of the Jan-Jun 2007, Jan-Jun 2008,Jan-Jun 2009,Jan-Jun 2010,Jan-Jun 2011.

I have set up the following MDX as the goal expression:

.8*((
([Measures].[Fatalities],
PARALLELPERIOD([Time].[Calendar].[Year],7,[Time].[Calendar].CurrentMember))
+
([Measures].[Fatalities],
PARALLELPERIOD([Time].[Calendar].[Year],6,[Time].[Calendar].CurrentMember))
+
([Measures].[Fatalities],
PARALLELPERIOD([Time].[Calendar].[Year],5,[Time].[Calendar].CurrentMember))
+
([Measures].[Fatalities],
PARALLELPERIOD([Time].[Calendar].[Year],4,[Time].[Calendar].CurrentMember))
+
([Measures].[Fatalities],
PARALLELPERIOD([Time].[Calendar].[Year],3,[Time].[Calendar].CurrentMember))

)/5

The problem is that the goal is taking the entire year average at the year level of the Calendar hierarchy. For example, in the attached screenshot, the Fatalities Goal would be empty when the fatalities is empty, and more importantly, the goal at the Year level of the Calendar hierarchy would only be the sum of Jan-Jun goal. The Fatalities Goal in this example should read 549.28 at the Calendar 2014 Year level and the status would still be green.

Screenshot

nsousa nsousa · Accepted Answer · 2014-08-27T13:14:39

You can do it with an Iif:

Iif( IsEmpty ([Measures].[Fatalities],[Time].[Calendar].CurrentMember) ), null, (...) )

That way only periods for which you have data would have a target.

[EDIT]

AS to your other question, how to get 2014 target calculated based on only the equivalent time periods for the previous 5 years. It's a bit trickier and requires defining a rather long calculated measure.

Step 1: Determine number of months for which you have data

WITH Member [Measures].[Months with Data] as Count( Filter( Descendants( [Time].[Calendar].[Year].[2014], [Time].[Calendar].[Month] ), NOT IsEmpty( [Measures].[Fatalities] ) ) )

Step 2: Define a given year's measure for the corresponding months

MEMBER [Measures].[Fatalities in Period] as Aggregate( Head( Descendants( [Time].[Calendar].CurrentMember, [Time].[Calendar].[Month] ), [Measures].[Months with Data] ) )

Step 3: Generate that value for the previous 5 years

MEMBER [Measures].[Target] as Avg( ( [Time].[Calendar].[Year].[2014].Lag(5) : [Time].[Calendar].[Year].[2014].Lag(1) ), [Measures].[Fatalities in Period] )

You may have to tweak quite a few things here, I wrote it from memory without testing, but this is the general idea.

Setting an MDX goal expression for baseline average in select months

1 Answers