Calculating the Max based on two criterias

[Altaibali]

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

[MAwwad]

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.