Return max monthly output value

[cyberdejy]

Hi,

I have a DAX for highest Daily and Monthly sales.

The daily formula works perfectly when whatever I select it just gives me the highest daily output - no problem.

But when I want to get the month with the highest output it just sums the months with the highest value from each year for some reason..

When I select from the Date slicer month by month is OK, but if I select 2 years or more or if there is no selection at all it returns the sum of all months instead of the highest month output.

Here are the calculations for both Max daily and Max Monthly:

Max Daily O/Put = MAXX(VALUES('Calendar'[Date].[Date]),CALCULATE(sum('COEX - List'[Output Kg]))) //this one is working without any issues

Max Monthly O/Put = MAXX(VALUES('Calendar'[Date].[Month]),CALCULATE(SUM('COEX - List'[Output Kg]))) //not working if selected two or more years from the slicer.

I don't know what I'm doing wrong..

Thanks

[Anonymous]

Hi  @cyberdejy ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
var _select = SELECTCOLUMNS('Table',"1",[Year])
return
FORMAT(
MAXX(
    FILTER(ALL('Table'),'Table'[Value]=
MAXX(
    FILTER(ALL('Table'),'Table'[Year] in _select),[Value])),[Date]),"mmmm")

2. Result:

Best Regards,

Liu Yang

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

[cyberdejy]

Hi and thanks for your feedback.

The way I see your example table - the highest value in year 2022 is (24) and the highest value in 2023 is (26) .. All I want is to have as a result when selecting both year 2022 and 2023 to get the highest value which is 26.

And if I select all three years then it should return (39) as the highest day value as per your example.

At the moment as it happend before with my tables when selecting multiple years it returns the sum instead of the single highest value accross all years.

[Thejeswar]

Hi @cyberdejy ,

I think the problem is what the DAX you have written.

I the DAX you have shared, there is a SUM function which I think is performing the sum of Values. In a day where there is a single entry, your DAX might work, but for days with multiple values for it, this should still be an issue

Try removing the SUM and see.

Max Daily O/Put = MAXX(VALUES('Calendar'[Date].[Date]),CALCULATE('COEX - List'[Output Kg]))

Max Monthly O/Put = MAXX(VALUES('Calendar'[Date].[Month]),CALCULATE('COEX - List'[Output Kg]))

Regards,

[cyberdejy]

Hi and thanks for the reply. It must have sum as its not a measure so it need to sum all the values in this column (Output Kg) and bring the max value for the selected period. For example if I select years 2023,2022,2021 etc. I want as ruturn the month with the highest value.