Forum Discussion

mglitta's avatar
mglitta
Regular Visitor
5 months ago
Solved

calculate with max() function

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

 

  • mglitta's avatar
    mglitta
    5 months ago

    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.

4 Replies

  • Demert's avatar
    Demert
    Resolver III
    5 months ago

    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's avatar
      mglitta
      Regular Visitor
      5 months ago

      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's avatar
        mglitta
        Regular Visitor
        5 months ago

        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.