Forum Discussion
GraceTCL
Helper II
5 years agoArgument '5' in CALCULATE function is required
Hi I have below code but it returns an error that "Argument '5' in CALCULATE function is required" and I am not sure what is wrong. Pls kindly advise. Thanks! GMV LW = Var CurrentYear = ...
- 5 years ago
Hi GraceTCL ,
You have a comma after the last Date ISO YEAR you need to take it.
formula corrected below:
GMV LW = VAR CurrentYear = IF ( HASONEVALUE ( 'Date'[ISO Year] ), VALUES ( 'Date'[ISO Year] ) ) VAR CurrentWeek = IF ( HASONEVALUE ( 'Date'[ISO Weeknumber] ), VALUES ( 'Date'[ISO Weeknumber] ) ) VAR LastWeekPY = CALCULATE ( MAX ( 'Date'[ISO Weeknumber] ), ALL ( 'Date' ), 'Date'[ISO Year] = CurrentYear - 1 ) VAR Result = IF ( CurrentWeek = 1, CALCULATE ( [GMV USD], ALL ( 'Date' ), 'Date'[ISO Weeknumber] = LastWeekPY, 'Date'[ISO Year] = CurrentYear - 1 ), CALCULATE ( [GMV USD], ALL ( 'Date' ), 'Date'[ISO Weeknumber] = CurrentWeek - 1, 'Date'[ISO Year] = CurrentYear ) ) RETURN result
MFelix
Super User
5 years agoHi GraceTCL ,
You have a comma after the last Date ISO YEAR you need to take it.
formula corrected below:
GMV LW =
VAR CurrentYear =
IF ( HASONEVALUE ( 'Date'[ISO Year] ), VALUES ( 'Date'[ISO Year] ) )
VAR CurrentWeek =
IF ( HASONEVALUE ( 'Date'[ISO Weeknumber] ), VALUES ( 'Date'[ISO Weeknumber] ) )
VAR LastWeekPY =
CALCULATE (
MAX ( 'Date'[ISO Weeknumber] ),
ALL ( 'Date' ),
'Date'[ISO Year] = CurrentYear - 1
)
VAR Result =
IF (
CurrentWeek = 1,
CALCULATE (
[GMV USD],
ALL ( 'Date' ),
'Date'[ISO Weeknumber] = LastWeekPY,
'Date'[ISO Year] = CurrentYear - 1
),
CALCULATE (
[GMV USD],
ALL ( 'Date' ),
'Date'[ISO Weeknumber] = CurrentWeek - 1,
'Date'[ISO Year] = CurrentYear
)
)
RETURN
result