Performance Point Relative Date Time Intelligence with current date time

In PerformancePoint, sometimes you want to override the date selected for the time intelligence formulae : for instance, you’re doing calculations for a rolling 3 months, and you want to change it to be rolling 3 months as at a certain date.

So this is definitely possible, if not entirely intuitive.

The key is to override the current date time.

NB: This technique can’t be used when overriding hierarchies in rows or columns

To start off, lets create a data connection to Adventure Works. Make sure to map the Time dimension to Date.Date.Calendar.

First, create a new Time Intelligence filter.

Create two formulae, “Rolling 3 Months” and “Rolling 12 Months”

The formulae will be: Month-2:Month and Month-11:Month as in the screenshot below

Figure 1

Call this Date Range Picker and save it

Next, create a new “Analysis Services” scorecard. Create the KPI with actual as Sales Amount, and the target as Sales Amount Quota. Call the KPI Sales Amount KPI.,

Create a new dashboard, and add the Date Ranger Picker filter to the header, and the new scorecard to the left column

Create a connection between the filter and the scorecard, and make sure to connect the Formula source to the TI Formula, as in the image below.

Figure 2

Deploy the dashboard to Sharepoint ~ you’ll note that the values are zero, because the Adventure Works cube has no data for 2011. Not very useful.

Next up, create a Time Intelligence Connection Formula filter, called Calendar Picker. Add it to the header of the dashboard, and create a connection from it to the scorecard. Make sure to connect Current Date Time in the source to Current Date Time, as in the image below.

Figure 3

Now deploy your dashboard to Sharepoint, and change the calendar picker to 1 January 2008.

And we have data J

Figure 4

And to prove it’s doing what it should, here’s the MDX from profiler

WITH

 MEMBER [Measures].[4f5bf6e3-23f9-4133-cdf3-ef3ce3f126e6_c7130074-4274-4432-8d36-e85b15b5bbfb] as '(Aggregate(NonEmpty({[Date].[Calendar].[Month].&[2007]&[3],[Date].[Calendar].[Month].&[2007]&[4],[Date].[Calendar].[Month].&[2007]&[5],[Date].[Calendar].[Month].&[2007]&[6],[Date].[Calendar].[Month].&[2007]&[7],[Date].[Calendar].[Month].&[2007]&[8],[Date].[Calendar].[Month].&[2007]&[9],[Date].[Calendar].[Month].&[2007]&[10],[Date].[Calendar].[Month].&[2007]&[11],[Date].[Calendar].[Month].&[2007]&[12],[Date].[Calendar].[Month].&[2008]&[1],[Date].[Calendar].[Month].&[2008]&[2]},[Measures].[Sales Amount Quota]),[Measures].[Sales Amount Quota]))'

 MEMBER [Measures].[4f5bf6e3-23f9-4133-cdf3-ef3ce3f126e6_334cce74-e28d-4c81-9b89-a083855d2347] as '(Aggregate(NonEmpty({[Date].[Calendar].[Month].&[2007]&[3],[Date].[Calendar].[Month].&[2007]&[4],[Date].[Calendar].[Month].&[2007]&[5],[Date].[Calendar].[Month].&[2007]&[6],[Date].[Calendar].[Month].&[2007]&[7],[Date].[Calendar].[Month].&[2007]&[8],[Date].[Calendar].[Month].&[2007]&[9],[Date].[Calendar].[Month].&[2007]&[10],[Date].[Calendar].[Month].&[2007]&[11],[Date].[Calendar].[Month].&[2007]&[12],[Date].[Calendar].[Month].&[2008]&[1],[Date].[Calendar].[Month].&[2008]&[2]},[Measures].[Sales Amount]),[Measures].[Sales Amount]))'

SELECT { [Measures].[4f5bf6e3-23f9-4133-cdf3-ef3ce3f126e6_c7130074-4274-4432-8d36-e85b15b5bbfb],

[Measures].[4f5bf6e3-23f9-4133-cdf3-ef3ce3f126e6_334cce74-e28d-4c81-9b89-a083855d2347]} ON AXIS(0)

FROM [Adventure Works]

Limitations : this doesn’t work if you are displaying the months in the rows or columns. Also some issues with the target, which will need to be investigated further.