Forum Discussion

ckhoo2007's avatar
ckhoo2007
Icon for Helper I rankHelper I
5 years ago
Solved

The MAX function only accepts a column reference as an argument.

Hi All,

I wrote a  DAX formula as follow to calculate previous week sales, instead of averaging over 7 days as the records are not the same each day I try to use SUMX function:

 

PW_Sales = IF(ISBLANK('APJ'[CW_Sales]), BLANK(),
(CALCULATE(DIVIDE(SUMX('APJ','APJ'[Sales]*'APJ'[CW_Record]),'APJ'[CW_Record]),
FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]-1)))))
 
However it returns an error:
The MAX function only accepts a column reference as an argument.
 
It works fine for calculating current week sales though:
CW_Sales = CALCULATE(DIVIDE(sumx('APJ','APJ'[Sales]*'APJ'[CW_Record]),'APJ'[CW_Record]),
FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
 
Appreciate your help how to fix this, thanks.
 
Regards,
Choo
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    ckhoo2007 , -1 is inside max , move outside

     

    PW_Sales = IF(ISBLANK('APJ'[CW_Sales]), BLANK(),
    (CALCULATE(DIVIDE(SUMX('APJ','APJ'[Sales]*'APJ'[CW_Record]),'APJ'[CW_Record]),
    FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))))

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    ckhoo2007 , -1 is inside max , move outside

     

    PW_Sales = IF(ISBLANK('APJ'[CW_Sales]), BLANK(),
    (CALCULATE(DIVIDE(SUMX('APJ','APJ'[Sales]*'APJ'[CW_Record]),'APJ'[CW_Record]),
    FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))))

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    ckhoo2007 you have subtract -1 after max

     

    PW_Sales = IF(ISBLANK('APJ'[CW_Sales]), BLANK(),
(CALCULATE(DIVIDE(SUMX('APJ','APJ'[Sales]*'APJ'[CW_Record]),'APJ'[CW_Record]),
FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))))

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • ckhoo2007's avatar
      ckhoo2007
      Icon for Helper I rankHelper I
      5 years ago

      Thanks Parry2k, I added -1 as I need to calculate previous week sales, it wrks fine when I calculate the average however it gives the above error when I changed the average to SumX.

       

      PW_Sales = IF(ISBLANK('APJ'[CW_Sales]), BLANK(),
      (CALCULATE(AVERAGE(APJ[Sales]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))))