ssrs column group subtraction from previous value

Question

I am using Microsoft report builder. I have columns in a column group that are grouped by weeks. One of the field of this scope should be subtraction of one column value from current week minus column value from another week. Is this possible in ssrs?

rough dataset:

Create table #Test
(
JobNum [nvarchar](20) 
,YearNumber int
,WeekNumber int
,Column1 int
)

insert into #Test 
VALUES
('job1',2022,1,10),
('job2',2022,1,50),
('job1',2022,2,15),
('job2',2022,2,60),
('job1',2022,3,20),
('job2',2022,3,70)

select * from #Test

drop table #Test

and groups in builder

Answer 1

Onestly I don't know if this is possible (I don't think is possbile to access the scope of the "previous group") but maybe you can use this workaroud.

0. Start dataset

I've started from this dataset, somehow similar to yours

And this is the tablix object

1. Create a calculated field on your dataset

Create a new simple filed in your dataset to have for each week number the previous week number

=Fields!Week.Value - 1

2. Add a custom function to sum the result of a LookupSet

Follow this guide to add this custom function to the report enabling us to sum the result of a LookupSet function (many thanks to the author!). We will use this in the next point.

Function SumLookup(ByVal items As Object()) As Decimal  
If items Is Nothing Then  
Return Nothing  
End If  
Dim suma As Decimal = New Decimal()  
Dim ct as Integer = New Integer()  
suma = 0  
ct = 0  
For Each item As Object In items  
suma += Convert.ToDecimal(item)  
ct += 1  
Next  
If (ct = 0) Then return 0 else return suma   
End Function

3. Add a costum expression for the column 2

= 
Sum(Fields!Value.Value) -
Code.SumLookup(LookupSet(Fields!PreviousWeekNumber.Value,Fields!Week.Value,Fields!Value.Value, "DataSet1"))

The LookupSet function retrive the set of values in the selected table/scope (Dataset1 in the example) wich have the week number equal to the previuos week number (in our the default scope, the column group scope). The custom function "SumLookup" enable us to sum the VariantArray (or Nothing if there is no match) returned by the LookupSet function.

4. Results

This is the result:

if you need a different result for the first week just add a condition to the custom expression for the column2

EDIT

If you have also a row group like this:

You can modify the previuos expression to this:

= 

Sum(Fields!Value.Value) -
Code.SumLookup(LookupSet(Fields!JobNum.Value & Fields!PreviousWeekNumber.Value,Fields!JobNum.Value & Fields!Week.Value,Fields!Value.Value, "DataSet1"))

Achivieng a LookupSet based on multiple conditions.