cancel
Showing results 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

New Member

## Calculating the Max based on two criterias

Hello All,

I have a daily stock report that I combine in Power query, and the report after combining would look like this:

 Date Calendar Week Contract Provider Available Stock 1/2/2023 2 66666 AOI 30 1/2/2023 2 55555 AOI 20 1/2/2023 2 44444 FBL 60 1/3/2023 2 66666 AOI 40 1/3/2023 2 55555 AOI 15 1/3/2023 2 44444 FBL 20

So the first 3 entries are from the report of 02.01.2023 and the next are from the report of 03.01.2023 and so on..

I need a dax formula to calculate the maximum stock available in every calendar week to use in a pivot table, by summing up the available stock for all the contracts everyday in the week and gives value of the maximum day, and your help on this is much appreciated.

Thank you,

Altaiba

Super User

To calculate the maximum stock available in every calendar week, you can use the following DAX formula:

Max Stock Available = MAXX( ADDCOLUMNS( GROUPBY( Table1, Table1[Calendar Week], Table1[Contract] ), "Total Stock", SUM(Table1[Available Stock]) ), [Total Stock] )

This formula first groups the data by the Calendar Week and Contract columns, and calculates the total stock available for each combination using the SUM function. It then creates a new column called "Total Stock" to store the calculated value.

The formula then uses the MAXX function to iterate over the grouped data and return the maximum value of the "Total Stock" column.

You can add this formula to your data model and use it in a pivot table to get the maximum stock available for each calendar week.

Announcements

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### 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
Top Kudoed Authors