When building a report or a dashboard, have you ever encountered a situation where the last day of data in a cube isn’t a consistent distance from the current day? For example, what if “Yesterday” is sometimes “Two Days Ago”? When building a KPI that displays the last day of data for a scorecard on SharePoint 2010 Dashboards, PerformancePoint Time Intelligence syntax such as (Day-1) cannot distinguish last non-empty values. Fortunately, the custom MDX function NonEmpty can be used in PerformancePoint Dashboard Designer to surmount such a challenge.
Let’s proceed with an example. As you can see in the PPS Dashboard Designer scorecard below, the last day of data in the cube is 11/1/2007. If PPS Time Intelligence syntax were set to “Day-1” instead of having the Date hierarchy on columns, you would see $1,871,531,455 if the current day were 11/2/2007. However if the current day were 11/3/2007, and 11/2/2007 happened to be empty, the value would be null:
In order to always have the display value be the last day with data, the following MDX syntax can be utilized in either the scorecard (Custom Set Formulas) or in the KPI itself (MDX Tuple Formula in Data Mappings):
Tail(NonEmpty([Time].[Calendar].[Calendar Date],[Measures].[Charges Per Day]),1).item(0)
The above MDX finds the last day for which there is data in the measure “Charges Per Day.” Once added as a Custom Set Formula to the scorecard, the 11/1/2007 value will show on the scorecard until there is a newer day with data: