Add delta into tablix SSRS

Question

This is type of my dataset:

| Date(dd/mm/yyyy) | Value |
|------------------|-------|
| 01.01.2018       | 50    |
| 01.01.2019       | 100   |
| 01.03.2019       | 200   |
| 01.05.2019       | 400   |

from this dataset i maked tablix:

| Date  | 01.01.2018|01.01.2019 | 01.03.2019 | 01.05.2019 |    Δ  |
|-------|-----------|-----------|------------|------------|-------|
| Value |50         |100        | 200        | 400        |300    |

How to put into tablix change value(+300) between 2 dates: 01.05.2019 -01.01.2019?

I used construction with lookup function, but it doesn't work:

made dataset with 2 dates:01.05.2019 and 01.01.2019
=lookup(min(Fields!Date.Value,"dataset2"), Fields!Date.Value, Fields!Value.Value, "dataset1")

If your dataset if ordered by date and you always want the first and last values then just use =Last(Fields!Value.Value) - First(Fields!Value.Value) — Alan Schofield
the max value is the last, but minimun doesn't. The first value in 2019 year — chipie

Alan Schofield Alan Schofield · Accepted Answer · 2019-05-06T16:05:41

If you add a parent column group call for example colgrpYear and set the grouping expression to be something like:

=YEAR(Fields!Date.Value)

This will group by the year -- you can hide the column group header if you like.

Then set the calculation expression to ..

=Last(Fields!Value.Value, "colgrpYear") - First(Fields!Value.Value, "colgrpYear")

This will scope the expression within the just the year that the cell resides in.

Note The scope must be in double quotes and is case sensitive.

Add delta into tablix SSRS

1 Answers