reporting-servicesssrs-groupingssrs-2016

SSRS 2016 - How to calculate a percentage difference between two values in a group?

I'm creating a report that shows the sales of item before a date range and after a date range.

The part I ran into trouble with is the percentage difference between the total sales on Date 1 and Date 2.

Items can have no sales for a certain week. The user can select multiple item ID's in the item ID parameter.

I can update the question to post my SQL query if needed.

What I've tried

Since I put a group on item ID I thought the First and Last functions would work.

Here's my expression on the column PCT.

=(Last(Fields!total_sales1.Value, "Date1")- First(Fields!total_sales1.Value, "Date1")) / First(Fields!total_sales1.Value, "Date1") * 100

But when I run the report I get the following results.

enter image description here

I need an expression on PCT column that will give me a percentage difference for each item pairs.

Solution

  • It looks like your scope is incorrect. Check what the rowgroup is called where you group by item id (let's say the row group is called "yourItemRowGroupName").

    Then change you expression to use that scope rather than "Date1".

    In fact you may not need the scope at all as it should work within the scope that expression sits (in your case, within your ItemID group.).

    So try 

    =(Last(Fields!total_sales1.Value)- First(Fields!total_sales1.Value)) / First(Fields!total_sales1.Value) * 100

    Or..

    =(Last(Fields!total_sales1.Value, "yourItemRowGroupName")- First(Fields!total_sales1.Value, "yourItemRowGroupName")) / First(Fields!total_sales1.Value, "yourItemRowGroupName") * 100

    You may have to handle divide by zero but try this to start with before you add any more complications.