# How to create KPI from numbers which the desire is trending down

### Question

• If we have a measure with different Descriptions (Paper Use vs Electronic replacement in document storage), how would we use figures from 2012 to show a downward trend visually of this usage going either up or down?

I am not even sure how to ask this question but I have a column called Total.  This stores numbers from different offices of, say, paper usage.  There must be an average across the company or across different offices which will show this column Total and how the figures are trending.

If, for instance, in our Kansas City office, in 2012 we used 13M pieces of paper and in 2013, we use 11.5M, am I able to create a KPI based on those 2012 numbers show show decline visually, other than a graph?

Still not making sense but I would also like to expand on this idea and show a decline for, say, a quarter vs last year same quarter.

Help!

Thanks.

Aaron

Tuesday, September 03, 2013 3:00 PM

• Aaron,

If you have a date table, you can take advantage of time intelligence functions to calculated measures based on previous time periods. In your case, you can use the following measures:

[Sum of Total]:=SUM(YourTable[Total])

[Total Prev Year]:=CALCULATE([Sum of Total], SAMEPERIODLASTYEAR(YourDateTable[DateColumn]))

[YOY Usage Trend]:=([Sum of Total]-[Total Prev Year])/[Total Prev Year]

and then use [YOY Usage Trend] to create your KPI. See http://technet.microsoft.com/en-us/library/hh272049.aspx for a similar example.

Hope this helps!

Taylor Clark

• Marked as answer by Wednesday, September 04, 2013 3:34 PM
Tuesday, September 03, 2013 5:21 PM

### All replies

• Aaron,

If you have a date table, you can take advantage of time intelligence functions to calculated measures based on previous time periods. In your case, you can use the following measures:

[Sum of Total]:=SUM(YourTable[Total])

[Total Prev Year]:=CALCULATE([Sum of Total], SAMEPERIODLASTYEAR(YourDateTable[DateColumn]))

[YOY Usage Trend]:=([Sum of Total]-[Total Prev Year])/[Total Prev Year]

and then use [YOY Usage Trend] to create your KPI. See http://technet.microsoft.com/en-us/library/hh272049.aspx for a similar example.

Hope this helps!

Taylor Clark

• Marked as answer by Wednesday, September 04, 2013 3:34 PM
Tuesday, September 03, 2013 5:21 PM
• I think I had simply missed something (SAMEPERIODLASTYEAR)

Works great, Taylor.  Thanks.

Aaron

Aaron

Wednesday, September 04, 2013 3:34 PM