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(IS...
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))))

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))))