Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Performance Point Relative Date Time Intelligence with current date time

  • 20 April 2011
  • Author: MarkGStacey
  • Number of views: 18678

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


 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.

Categories: Blogs
Rate this article:
No rating


Other posts by MarkGStacey

Please login or register to post comments.