Calculate measure of max of value per month

[DIAS_TRINDADE]

DATE		Ducks		Max Ducks (Month)
2022-01-01		10		23
2022-01-02		2		23
2022-01-03		23		23
2022-02-01		2		10
2022-02-02		10		10

Given above table, I would want to calculate max number of ducks in a certain month. I have initially the first two columns, and I tried to get the third with this formula

CALCULATE(MAX(PARK[DUCKS]),ALLEXCEPT(PARKS, PARKS[DATE].[MonthNo]))

But this is instead what I'm getting

DATE		Ducks		Max Ducks (Month)
2022-01-01		10		23
2022-01-02		2		23
2022-01-03		23		23
2022-02-01		2		23
2022-02-02		10		23

Could anyone help me here pls? What would be the right measure to use?

[tamerj1]

Hi @DIAS_TRINDADE 

create month number as a column. But if you have multiple years then create year month number column and use it in ALLEXCEPT. It should work 

[v-xiaotang · Accepted solution]

Hi @DIAS_TRINDADE 

Thanks for reaching out to us.

You can try this measure, 

Measure = MAXX(FILTER(ALL('Table'), YEAR('Table'[DATE])=YEAR(MIN('Table'[DATE])) && MONTH('Table'[DATE])= MONTH(MIN('Table'[DATE]))),[Ducks])

then it returns max ducks according to the year and month of current row.

result

Best Regards,

Community Support Team _Tang

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