cancel
Showing results for
Did you mean:
Helper I

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

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

1 ACCEPTED SOLUTION
Super User

@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

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
2 REPLIES 2
Community Support

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.

Super User

@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

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!