measure to return/filter 3 columns

[Anonymous]

Hi guys,

I am trying to return and matrix table with 3 years as columns: last_year, this_year and next_year

I made the following measure:

Tons measure =

var this_year = YEAR(TODAY())

var previous_year = YEAR(TODAY())-1

var next_year = YEAR(TODAY()) +1

Return

SUMX(FILTER('Test Tabel', 'Test Tabel'[Date]={this_year, previous_year, next_year}),'Test Tabel'[Tons]
)

I get the error:  A table of multiple values was supplied where a single value was expected.

The expected result is :

Result tabel 1
name	2020	2021	2022
a	1	4	7
b	2	5	8
c	3	6	9

Any help/tips are appreciated. Thanks in advance

[amitchandak · Accepted solution]

@Anonymous ,  Try one of the two ways

Tons measure =
var this_year = YEAR(TODAY())
var previous_year = YEAR(TODAY())-1
var next_year = YEAR(TODAY()) +1
Return
SUMX(FILTER('Test Tabel', 'Test Tabel'[Date] in {this_year, previous_year, next_year}),'Test Tabel'[Tons]
)

or

Tons measure =
var this_year = YEAR(TODAY())
var previous_year = YEAR(TODAY())-1
var next_year = YEAR(TODAY()) +1
Return
SUMX(FILTER('Test Tabel', 'Test Tabel'[Date]>= previous_year && 'Test Tabel'[Date]<= next_year}),'Test Tabel'[Tons]
)