topic Re: calculate with max() function in DAX Commands and Tips

calculate with max() function

mglitta — Tue, 10 Mar 2026 17:50:41 GMT

Hi,

I try to do simple calc where instead of fixed value i want to use function (max)

formula with fixed value works

REV_CW =

VAR REV_CW = CALCULATE(SUM('LOAD'[REVENUE_ACT]), 'LOAD'[TYPE] IN { "LOAD" } && 'LOAD'[WEEK] IN {11})

RETURN

REV_CW

when i replace fixed value with function max(), it returns all rows, but total shows correctly

REV_CW =

VAR REV_CW = CALCULATE(SUM('LOAD'[REVENUE_ACT]), 'LOAD'[TYPE] IN { "LOAD" } && 'LOAD'[WEEK] IN {MAX(LOAD[WEEK]) })

RETURN

REV_CW

Re: calculate with max() function

Demert — Tue, 10 Mar 2026 21:16:15 GMT

Hi mglitta

Can you try a few things?

Check if Max(load[week]) actually returns 11, inside your table visual?

Also perhaps change your code to :
"

REV_CW = VAR MAXWEEK = MAX(LOAD[WEEK]) VAR REV_CW = CALCULATE(SUM('LOAD'[REVENUE_ACT]), 'LOAD'[TYPE] IN { "LOAD" } && 'LOAD'[WEEK] = MAXWEEK, 'LOAD'[WEEK] <> BLANK() RETURN REV_CW

Re: calculate with max() function

mglitta — Tue, 10 Mar 2026 21:59:26 GMT

thanks Demert for your advice, I tried and it didnt help.

yes, MAX(LOAD[WEEK]) returns value 11, i test the formula in a card.

i suspect that the problem might be that i calculate revenue in the same table where i define the parameter from. I have to figure out the other way how to define week number parameter

Re: calculate with max() function

mglitta — Tue, 10 Mar 2026 22:10:45 GMT

just tested my assumption. created a side table with only week numbers from data table. made a max() function on week number and used in calculation and it works fine. simply you cant have calculation and parameter defined from the same table.

Re: calculate with max() function

v-ssriganesh — Wed, 11 Mar 2026 04:48:53 GMT

Hello mglitta,
Thanks for the update and for sharing the solution.

It’s good to hear that using a separate table for the week parameter resolved the issue. This will assist other community members in resolving similar issues more efficiently.
Also thanks to Demert for the earlier suggestions.

Best regards,
Ganesh Singamshetty.