How to sum last year’s values that are relevant this year

[L_K_]

Hello,

I have a table for my suppliers and their values over the years (It is a simplified example).

Company	Value	Year
A	10	2022
A	12	2022
A	11	2021
A	14	2021
B	13	2022
B	10	2022
B	9	2022
B	8	2021
B	12	2021
B	6	2021
C	8	2021
C	4	2021

Now if we use a matrix to show the sum of values per company and year, we get the next table:

Company	2021	2022
A	25	22
B	26	32
C	12
Total	63	54

Now I put the year in a slicer to jump between 2022 and 2021 and made a measure to show me last year’s values for the comparison of values for each company.

Value_last_year = IF(NOT(ISBLANK(TABLE[VALUE])),CALCULATE((TABLE[VALUE]),'DATE'[YEAR]= SELECTEDVALUE('DATE'[YEAR])-1),BLANK())

Which gives me the next result:

Company	Last year	This year
A	25	22
B	26	32
Total	63	54

My problem is that I only want to summarize last year’s values in the measure for the companies that have a value this year. Like Company C doesn’t have values this year, but it still counts that value in the total row.

So, I would like my sum total to look like this:

Company	Last year	This year
A	25	22
B	26	32
Total	51	54

How can I make a measure that summarizes last years values, but only for the companies that have a value this year?

I also have a lot of data, so I don’t want to make a separate table for the measures.

Thank you.

[tamerj1]

Hi @L_K_ 

please try

Value_last_year =
CALCULATE (
    SUM ( TABLE[VALUE] ),
    KEEPFILTERS ( 'DATE'[YEAR] = SELECTEDVALUE ( 'DATE'[YEAR] ) - 1 )
)

[L_K_]

Hi,

no, this doesn't work since it's kind of the same as I already have. 

Your calculation does not exclude last years values from companies that don’t have a value this year. Which is the answer I am looking for.

[tamerj1]

Hi @L_K_ 
Esiest to use the filter pane

[L_K_]

Filtering doesn't work for my actual set of data.

Thank you though.

[tamerj1 · Accepted solution]

@L_K_ 

Here is the solution as wish https://www.dropbox.com/t/Xckbmy4zeHiGVQBv

[L_K_]

Thank you so much @tamerj1

I really appreciate it! This works better.