Is it possible to score a KPI based on todays date?

Question

I am building a KPI in PerformancePoint - SharePoint 2010 and have the following setup:

We have some files that are stored and they have a "Review/Expiry Date" field. Is it possible to get the KPI to display red if the review date is less than todays date?

I thought this might be something simple but it's turning out to be really hard to find a solution that I can use in PerformancePoint.

I can build the functionality into XML but that would then seperate these files from all the other KPI's I have!

Thanks a million in advance for any assistance.

Rich

Answer 1

The answer to your question is heavily dependent on the type of data source you're using. If you have your data in a SQL Server table, it should be trivial to add another column that generates a "days left to complete" or something, and you can set your target thresholds accordingly.

So I would create a table that looks like this:

Item   | Review Date | Days Remaining
Task 1 | 10/01/2011  | 95
Task 2 | 1/1/2011    | -30
Task 3 | 2/28/2011   | 12

The Days Remaining column would just be something like

SELECT DATEDIFF("dd", [Review Date], GETDATE())

If you want to do this in a SharePoint list, you can also accomplish this easily. I created a simple task list and added a Calculated Column called Remaining Days. I used the following formula:

=[Due Date]-[Today]

When you create your KPI, just create a Target for Days Remaining. You want Larger is Better, Green is anything above 5 days, Yellow between 1 and 5, and Red is anything less than 1.

The ease of this really depends on your data source. Good luck.