cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Davidkm
Frequent Visitor

Find year where is Max profit as sum profit by year

Hi, 

I would need a measure that will give me back the year when the biggest profit was. Data as table below.

 

Thx

 

David

 

 

IDDateProfit
11.1.20205
21.2.20204

3

5.10.2020

6

4

6.5.2018

8

5

6.4.2019

9
 

...

 

 

1 ACCEPTED SOLUTION
Davidkm
Frequent Visitor

I found resolution:

year_with_max_profit = 

var distinc_table = ADDCOLUMNS(KEEPFILTERS(VALUES('Table'[Date].[Year])), "profit", CALCULATE(COUNTA('Table'[id])))
var maxobj = MAXX(
	distinc_table,
	CALCULATE(COUNTA('Table'[id]))
    )

return CALCULATE(MIN('Table'[Year]), FILTER(distinc_table , [profit] = maxobj))

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

Hi  @Davidkm ,

Here are the steps you can follow:

1. Create calculcated column.

Year = YEAR('Table'[Date])

Month = MONTH('Table'[Date])

2. Create measure.

Max_Month =
var _1=
MAXX(ALL('Table'),'Table'[Profit])
return CALCULATE(MAX('Table'[Year]),FILTER('Table','Table'[Profit]=_1))

3. Result.

v-yangliu-msft_0-1605834996539.jpeg

You can downloaded PBIX file from here.

 

Best Regards,

Liu Yang

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

 

Hi Liu, 

the assignment is different. I wanted to know the year where the amount of profit for the whole year is the largest.

D.

 

Davidkm
Frequent Visitor

I found resolution:

year_with_max_profit = 

var distinc_table = ADDCOLUMNS(KEEPFILTERS(VALUES('Table'[Date].[Year])), "profit", CALCULATE(COUNTA('Table'[id])))
var maxobj = MAXX(
	distinc_table,
	CALCULATE(COUNTA('Table'[id]))
    )

return CALCULATE(MIN('Table'[Year]), FILTER(distinc_table , [profit] = maxobj))

View solution in original post

amitchandak
Super User IV
Super User IV

@Davidkm , with help from date table take max of profit

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below


This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi, 

this is not what I want. I need get year and not sum profit. In my example i need get 2020.

thx

 

David

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.