topic Re: calculate profit only if item exists in two years in DAX Commands and Tips

calculate profit only if item exists in two years

AnthonyJoseph — Wed, 07 Sep 2022 17:50:22 GMT

Hello,

I would need to calculate profit for different items for the slected year and its previous year based on a condition (i.e. calcuate profit for items that were present in the selected year and its previous year).

My input table is like below:

Id	Item	Year	Profit	Sales
1	A	2020	143	936
2	A	2021	191	286
3	A	2022	940	652
4	B	2020	421	774
5	B	2021	1000	796
6	C	2020	466	870
7	C	2021	153	945
8	C	2022	640	928
9	D	2021	594	909
10	D	2022	259	622
11	E	2021	726	330
12	F	2021	379	157
13	F	2020	192	148
14	F	2022	156	376

when year 2021 is selected, I want to list down the items that are common in both the years 2020 and 2021, then calculate their sums. Pasted below is the expected result.

Please can somone guide me here.

Thanks,

AnthonyJoseph

Re: calculate profit only if item exists in two years

tamerj1 — Wed, 07 Sep 2022 18:44:06 GMT

AnthonyJoseph

Please try

2021 Profit = VAR SelectedYear = SELECTEDVALUE ( TableName[Year] ) VAR CarrentItems = VALUES ( TableName[Item] ) VAR PreviousItems = CALCULATETABLE ( VALUES ( TableName[Item] ), TableName[Year] = SelectedYear - 1 ) VAR CommonItems = INTERSECT ( CarrentItems, PreviousItems ) RETURN CALCULATE ( SUM ( TableName[Profit] ), CommonItems )

Re: calculate profit only if item exists in two years

AnthonyJoseph — Wed, 07 Sep 2022 19:18:22 GMT

Thanks tamerj1 . The solution worked for 2021 year selection but I'm not able to get the desired number for 2020 by modifing the current dax to 2020. Please can you share your thoughts on this as well...

Re: calculate profit only if item exists in two years

tamerj1 — Thu, 08 Sep 2022 02:43:29 GMT

AnthonyJoseph

Do you have data for 2019?

Re: calculate profit only if item exists in two years

AnthonyJoseph — Fri, 09 Sep 2022 05:16:21 GMT

Hi tamerj1 There is no data for 2019 but when I select 2021 in the slicer then the output should display both present year value and the prior year value.

Based on the measure, I was able to get the present year value for 2021 but unable to get the value for 2020.

I am trying to show difference between the values in 2021 and 2020 showing only the common items that are there in both years... i.e. when I select 2021 in the slicer it should automatically show the value for its prior year 2020 and present year 2021 ....

Can you please help me to get the values for column "Profit in 2020" column --highlighted in red.

Re: calculate profit only if item exists in two years

tamerj1 — Fri, 09 Sep 2022 05:58:08 GMT

Hi AnthonyJoseph
I got your point. Let's do it in a more dynamic way. The two measures will be "Selected Year Profit" and "Previous to Selected Year Profit" as follows

Selected Year Profit = VAR SelectedYear = SELECTEDVALUE ( TableName[Year] ) VAR CarrentItems = VALUES ( TableName[Item] ) VAR PreviousItems = CALCULATETABLE ( VALUES ( TableName[Item] ), TableName[Year] = SelectedYear - 1 ) VAR CommonItems = INTERSECT ( CarrentItems, PreviousItems ) RETURN CALCULATE ( SUM ( TableName[Profit] ), CommonItems )

Previous Year Profit = VAR SelectedYear = SELECTEDVALUE ( TableName[Year] ) - 1 VAR CarrentItems = VALUES ( TableName[Item] ) VAR PreviousItems = CALCULATETABLE ( VALUES ( TableName[Item] ), TableName[Year] = SelectedYear - 2 ) VAR CommonItems = INTERSECT ( CarrentItems, PreviousItems ) RETURN CALCULATE ( SUM ( TableName[Profit] ), CommonItems )

Re: calculate profit only if item exists in two years

AnthonyJoseph — Mon, 12 Sep 2022 14:50:05 GMT

Thanks tamerj1 I tried using two measures but still not getting the previous year value.... Pasted below the screenshot for the measures:

Thanks,

AnthonyJoseph

Re: calculate profit only if item exists in two years

tamerj1 — Mon, 12 Sep 2022 14:51:48 GMT

AnthonyJoseph

Have you selected a year?

Re: calculate profit only if item exists in two years

AnthonyJoseph — Mon, 12 Sep 2022 17:10:58 GMT

Yes, I have selected 2021 year... If incase, multiple years are selected then both value appears blank.

Re: calculate profit only if item exists in two years

tamerj1 — Mon, 12 Sep 2022 19:33:30 GMT

AnthonyJoseph
Would you please provide dummy sample as per the original source data?

Re: calculate profit only if item exists in two years

Whitewater100 — Tue, 13 Sep 2022 00:53:54 GMT

Hi:

You can try this. I hope it helps!

https://drive.google.com/file/d/1nOZcrJW3WLN-Dlz0-gCAyRU7nbFrqilf/view?usp=sharing

Re: calculate profit only if item exists in two years

v-xiaotang — Wed, 14 Sep 2022 07:31:01 GMT

Hi AnthonyJoseph

Thanks for reaching out to us.

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Re: calculate profit only if item exists in two years

AnthonyJoseph — Fri, 16 Sep 2022 06:09:34 GMT

Hi tamerj1 The sample data is pasted below and I would be very excited to solve this with measures....

Id	Item	Year	Profit	Sales
1	A	2020	143	936
2	A	2021	191	286
3	A	2022	940	652
4	B	2020	421	774
5	B	2021	1000	796
6	C	2020	466	870
7	C	2021	153	945
8	C	2022	640	928
9	D	2021	594	909
10	D	2022	259	622
11	E	2021	726	330
12	F	2021	379	157
13	F	2020	192	148
14	F	2022	156	376

Re: calculate profit only if item exists in two years

tamerj1 — Fri, 16 Sep 2022 06:52:36 GMT

Hi AnthonyJoseph
I think I did a very stupid mistake in the Previous Year Measure. Please refer to attached sample file

Selected Year Profit = VAR SelectedYear = SELECTEDVALUE ( 'Table'[Year] ) VAR CarrentItems = VALUES ( 'Table'[Item] ) VAR PreviousItems = CALCULATETABLE ( VALUES ( 'Table'[Item] ), 'Table'[Year] = SelectedYear - 1 ) VAR CommonItems = INTERSECT ( CarrentItems, PreviousItems ) RETURN CALCULATE ( SUM ( 'Table'[Profit] ), CommonItems )

Previous Year Profit = VAR SelectedYear = SELECTEDVALUE ( 'Table'[Year] ) - 1 VAR CarrentItems = VALUES ( 'Table'[Item] ) VAR PreviousItems = CALCULATETABLE ( VALUES ( 'Table'[Item] ), 'Table'[Year] = SelectedYear - 1 ) VAR CommonItems = INTERSECT ( CarrentItems, PreviousItems ) RETURN CALCULATE ( SUM ( 'Table'[Profit] ), CommonItems )

Re: calculate profit only if item exists in two years

AnthonyJoseph — Wed, 05 Oct 2022 17:51:42 GMT

Hi tamerj1

Thank you so much for your response. Sorry to share that the result wasnt the intended one but it was able to modify the measures and was able to achieve the intended results.

I created a disconnected Year table and used the below measures.

Profit in current year = VAR SelectedYear = SELECTEDVALUE( 'Year'[Year] ) VAR CurrentItems = VALUES ( 'Table'[Item] ) VAR PreviousItems = CALCULATETABLE ( VALUES ( 'Table'[Item] ), 'Table'[Year] = SelectedYear - 1 ) VAR CommonItems = INTERSECT ( CurrentItems, PreviousItems ) RETURN {CALCULATE(sum('Table'[profit]),FILTER('Table', 'Table'[Item] in CommonItems), FILTER('Table', 'Table'[Year] = selectedyear) )}

Profit in prior year = VAR SelectedYear = SELECTEDVALUE( 'Year'[Year] )-1 VAR CurrentItems = VALUES ( 'Table'[Item] ) VAR PreviousItems = CALCULATETABLE ( VALUES ( 'Table'[Item] ), 'Table'[Year] = SelectedYear + 1 ) VAR CommonItems = INTERSECT ( CurrentItems, PreviousItems ) RETURN {CALCULATE(sum('Table'[Profit]),FILTER('Table', 'Table'[Item] in CommonItems), FILTER('Table', 'Table'[Year] = selectedyear))}

Results:

Thanks,

AnthonyJoseph