cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

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

Top Solution Authors
Top Kudoed Authors