How to intersect employees ID between two years and obtain the amount of cost in the current year

[fabb995]

Hi all,

I have a dataset that contains the list of employees ID and their cost.

I want to create a calculated measure that intersect employees ID that exist in 2021 and 2022

2021
Employee ID	Cost
1	10
2	15
4	20

2022
Employee ID	Cost
1	20
2	30
5	40

and extract their total cost ONLY in 2022 (equal to the sum of  the cost of Employee ID 1 and 2, 20+30=50).

Using this set analysis expression, 

SUM({<[Employee ID] = P({<YEAR={2022}>})*P({<YEAR={2021}>})>}[Cost])

I can extract the total cost of employees ID 1 and 2 (75) but not the cost related to 2022 for employees ID 1 and 2 (50 would be the right value).

Using the next set analysis expression I can't extract the right value:

SUM({<YEAR={2022}>}{<[Employee ID] = P({<YEAR={2022}>})*P({<YEAR={2021}>})>}[Cost])

How I can correct the set analysis expression? It exists another way to reach my goal?

Thank you in advance for the support,

Fabio.

[rubenmarin]

Hi, you were close, the syntax to use two filters is separating fields by comma:

SUM({<YEAR={2022},[Employee ID] = P({<YEAR={2022}>})*P({<YEAR={2021}>})>}[Cost])

[Vegar · Accepted solution]

 I'm not sure you need the P({<YEAR={2022}>}). You will get that reduction from your YEAR={2022}. I think you should be able to simplify it to this expression:

SUM({<YEAR={2022},[Employee ID] = P({<YEAR={2021}>})>}[Cost])

[fabb995]

Thanks for the answer, but this formula gives me a null value.

Fabio

[fabb995]

Thank you, this is the right formula.

Fabio

[rubenmarin]

Hi, it worked for me, sample attached.

Anyway, Vegar answer is better, I just focused on the syntax.