topic Re: How to intersect employees ID between two years and obtain the amount of cost in the current year in App Development

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

fabb995 — Fri, 29 Apr 2022 15:19:51 GMT

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.

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

rubenmarin — Fri, 29 Apr 2022 18:35:55 GMT

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])

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

Vegar — Sat, 30 Apr 2022 21:57:10 GMT

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])

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

fabb995 — Mon, 02 May 2022 08:33:13 GMT

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

Fabio

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

fabb995 — Mon, 02 May 2022 08:33:59 GMT

Thank you, this is the right formula.

Fabio

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

rubenmarin — Mon, 02 May 2022 08:40:33 GMT

Hi, it worked for me, sample attached.

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