Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
AnthonyJoseph
Resolver III
Resolver III

calculate profit only if item exists in two years

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:

IdItemYearProfitSales
1A2020143936
2A2021191286
3A2022940652
4B2020421774
5B20211000796
6C2020466870
7C2021153945
8C2022640928
9D2021594909
10D2022259622
11E2021726330
12F2021379157
13F2020192148
14F2022156376

 

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.

 

AnthonyJoseph_0-1662572720472.png

Please can somone guide me here.

 

Thanks,

AnthonyJoseph

2 ACCEPTED SOLUTIONS
Whitewater100
Solution Sage
Solution Sage

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:

AnthonyJoseph_0-1664992051523.png

AnthonyJoseph_1-1664992079954.png

Thanks,

AnthonyJoseph

 

View solution in original post

14 REPLIES 14
v-xiaotang
Community Support
Community Support

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.

Whitewater100
Solution Sage
Solution Sage

tamerj1
Super User
Super User

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

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...

@AnthonyJoseph 

Do you have data for 2019?

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 ....

 

AnthonyJoseph_0-1662700349829.png

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

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 )

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

AnthonyJoseph_0-1662994108414.png

Thanks,

AnthonyJoseph

@AnthonyJoseph 

Have you selected a year?

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

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

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

IdItemYearProfitSales
1A2020143936
2A2021191286
3A2022940652
4B2020421774
5B20211000796
6C2020466870
7C2021153945
8C2022640928
9D2021594909
10D2022259622
11E2021726330
12F2021379157
13F2020192148
14F2022156376

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

1.png

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 )

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:

AnthonyJoseph_0-1664992051523.png

AnthonyJoseph_1-1664992079954.png

Thanks,

AnthonyJoseph

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors