calculate with max() function

[mglitta]

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

[Demert]

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

[mglitta]

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

[mglitta · Accepted solution]

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.

[v-ssriganesh]

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.