add calculated fields based on year by year performance

Question

I am generating a report about sales and want to know if there is a way to calculate an aggregated column based on dates. e.g. Data stored in a matrix is orders, column is month and calender year. each month shows 3 years. I want the calculated field to take the most recent months and calculate the percentage change between them. see the following image to understand what I mean.

As you can see there are 3 years. I want to ignore 2008 and wnat my calculated field to do the percentage value of increase from 2009 to 2010. The design of this is shown in following image.

Regarding the other question, see the following images:

In the design view my matrix looks like this:

The output is report is:

The original data is:

And I just want to see the recent 2 years and ignore the rest of the years, like follows:

Thank you all in advance

alejandro zuleta alejandro zuleta · Accepted Answer · 2015-11-23T12:31:14

Supposing your dataset returns one row per month/year you cannot use a calculated field.

However, you can calculate the increment or decrement percentage using an expression.

Add a column to the right under the Month column group.

Add the following expression to the cell is highlighted in my matrix.

=(Sum(iif(Fields!Year.Value=2010,Fields!Total_Ord.Value,0))-
Sum(iif(Fields!Year.Value=2009,Fields!Total_Ord.Value,0))
)/Sum(iif(Fields!Year.Value=2009,Fields!Total_Ord.Value,0))

Note my formula is (2010-2009)/2009 it will return a positive percentage if 2010 Total_Ord is greater than the 2009 Total_Ord. Otherwise the percentage will be negative.

This is the preview of the matrix:

UPDATE: Calculate percentage inc/dec of the last year and the last previous year.

Use this expression to compare the last year in my case 2015 and the last previous year 2014:

=(Sum(iif(Fields!Year.Value=Max(Fields!Year.Value),Fields!Sales.Value,0))-
Sum(iif(Fields!Year.Value=Max(Fields!Year.Value)-1,Fields!Sales.Value,0))
)/Sum(iif(Fields!Year.Value=Max(Fields!Year.Value)-1,Fields!Sales.Value,0))

It will preview this matrix:

Let me know if this can help you.

add calculated fields based on year by year performance

1 Answers