Forum Discussion

bo_afk's avatar
bo_afk
Post Patron
7 years ago
Solved

Calculate formula with filter Max

Hi,

 

I would like to write a formula to calculate the total sales for the current year. In my data, I have Year, Weeknumber, productid and sales.

 

I've used the CALCULATE formula as per below:
Cur Year Sales = CALCULATE(sum('Table'[sales]),'Table'[Year] = MAX('Table'[Year]))

 

However, the MAX syntax is apparently not allowed in this expression.

 

Can someone help?

 

Thanks!

afk

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    bo_afk 

     

    You might need to take the MAX expression out of Filter expression of CALCULATE

     

    Cur Year Sales =
VAR maxyear =
    MAX ( 'Table'[Year] )
RETURN
    CALCULATE ( SUM ( 'Table'[sales] ), 'Table'[Year] = maxyear )

1 Reply

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    bo_afk 

     

    You might need to take the MAX expression out of Filter expression of CALCULATE

     

    Cur Year Sales =
VAR maxyear =
    MAX ( 'Table'[Year] )
RETURN
    CALCULATE ( SUM ( 'Table'[sales] ), 'Table'[Year] = maxyear )