Calculate the max value by category

syelify 

you can do the data transform in PQ

1. change date to the first day of month

2. group by data

then you can get the output

However, the output is different from yours. 

could you pls explain why you don't count 63-65 to Jan?

Thank you, would it be possible to have the Dax measure

create two columns

month = FORMAT('Table'[Date],"mmm")

shelves2 = if('Table'[Shelves]=CALCULATE(max('Table'[Shelves]),ALLEXCEPT('Table','Table'[month],'Table'[Door])),'Table'[Shelves],0)

 

Hi syelify 

Thanks to ryan_mayu  for your timely and effective response, here are my additions:

You can create two measures as follow.

max = 
VAR _month = MONTH(MAX([Date]))
VAR _max = CALCULATE(MAX([Shelves]), FILTER(ALLEXCEPT('Table', 'Table'[Door]), MONTH([Date]) = _month))
RETURN
_max

sum = CALCULATE(SUM('Table'[Shelves]), FILTER('Table', [Shelves] = [max]))

Change Date to a hierarchical structure

Output:

Best Regards,
Yulia Xu

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