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

[ckhoo2007]

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

[parry2k]

@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]

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

[amitchandak · Accepted solution]

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

[ckhoo2007]

Thanks!