Power BI Dax Find max of a column by supplier and year.

[Nathan__Mox]

Hey all,

Im trying to make a new column with the max value of another column by supplier and year.

Supplier	Year	Amount	Max
A	2022	5000	7000
A	2022	2000	7000
A	2022	7000	7000
A	2023	4000	4000
A	2023	2000	4000
B	2022	5000	6000
B	2022	6000	6000
B	2022	2000	6000
B	2023	1500	1500

Hope this makes sense, let me know if you need anymore information.

Any help would be very much appreciated.

Thanks 

Nathan 

[v-zhangti]

Hi, @Nathan__Mox 

You can try the following methods.

Column = CALCULATE(MAX('Table'[Amount]),ALLEXCEPT('Table','Table'[Supplier],'Table'[Year]))

Or

Column 2 = CALCULATE(MAX('Table'[Amount]),FILTER('Table',[Supplier]=EARLIER('Table'[Supplier])&&[Year]=EARLIER('Table'[Year])))

Measure:

Measure = CALCULATE(MAX('Table'[Amount]),ALLEXCEPT('Table','Table'[Supplier],'Table'[Year]))

Or

Measure 2 = CALCULATE(MAX('Table'[Amount]),FILTER('Table',[Supplier]=SELECTEDVALUE('Table'[Supplier])&&[Year]=SELECTEDVALUE('Table'[Year])))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[amitchandak · Accepted solution]

@Nathan__Mox , A new column in DAX

max=  maxx(filter(Table, [Supplier] = earlier( [Supplier]) && [Year] = earlier( [Year]) ), [Amount])

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8