Dateadd function returning error

[dokat]

Hi,

I am trying to create a calculate dcolumn using values in Calender Year Column. To calculate YTDLY i am using below dateadd function line but it returns following error. Can anyone have solve for this problem?

"The syntax for ',' is incorrect. (DAX(var maxdate = calculate(max(CY[Calendar Year]),all())return switch(true(), CY[Calendar Year]= maxdate,"YTD",CY[Calendar Year]= dateadd(var maxdate, -1,year),"YTDLY")))."

CY[Calendar Year]= dateadd(var maxdate, -1,year),"YTDLY") -> however it gives 

Slicer = var maxdate = calculate(max(CY[Calendar Year]),all())return switch(true(), 
CY[Calendar Year]= maxdate,"YTD",
CY[Calendar Year]= dateadd(var maxdate, -1,year),"YTDLY")

[PhilipTreacy]

@dokat 

Please supply your file otherwise we have to recreate everything by hand.

regards

Phil

[dokat · Accepted solution]

@PhilipTreacy Please disregard my questions. I modified the code and below worked for me.

Slicer = var maxdate = calculate(max(CY[Calendar Year]),all())return switch(true(), 
CY[Calendar Year]= maxdate,"YTD",
CY[Calendar Year]= maxdate-365,"YTDLY",
CY[Calendar Year]= DATE ( YEAR (maxdate)-1, 12, 31 ),"Last Year",
CY[Calendar Year]= DATE ( YEAR (maxdate)-2, 12, 31 ),"Last YearLY",
and(MONTH(CY[Calendar Year]) = month(maxdate),YEAR(CY[Calendar Year]) = YEAR(maxdate)),"Last Month",
and(MONTH(CY[Calendar Year]) = month(maxdate),YEAR(CY[Calendar Year]) = Year(maxdate)-1),"Last MonthLY")

[dokat]

@PhilipTreacy I uploaded the file to dropbox. Please see below

https://www.dropbox.com/s/lj93ly0uw1utq20/Test.pbix?dl=0

[TheoC]

Hi @dokat 

Is there a reason the "ALL" function does not have a table or column name (just before the RETURN in your first line of code)?

Cheers,

Theo

[dokat]

@TheoC I modified the code slightly and it worked FOR ytdly however now I can't figure out Last MonthLY value. Please see below screenshot. I am trying to 01/01/2021 should be Last MonthLY

Slicer = var maxdate = calculate(max(CY[Calendar Year]),all())return switch(true(), 
CY[Calendar Year]= maxdate,"YTD",
CY[Calendar Year]= maxdate-365,"YTDLY",
CY[Calendar Year]= DATE ( YEAR (maxdate)-1, 12, 31 ),"Last Year",
CY[Calendar Year]= DATE ( YEAR (maxdate)-2, 12, 31 ),"Last YearLY",
and(MONTH(CY[Calendar Year]) = month(maxdate),YEAR(CY[Calendar Year]) = YEAR(maxdate)),"Last Month", 
and(MONTH(CY[Calendar Year]) = month(maxdate)-12,YEAR(CY[Calendar Year]) = YEAR(maxdate)),"Last MonthLY")

[dokat]

I am trying to get the max date based on selection of slicers thats wy there is not table for all.

[PhilipTreacy]

Hi @dokat 

I'm a bit unclear about the code you've written, it's always best to supply the file you are using, but, when you declare a VAR you don't subsequently reference it using the VAR keyword.  You also need to supply a default return value for SWITCH.  Try this

Slicer = 

VAR maxdate = calculate(max(CY[Calendar Year]),all())

RETURN SWITCH( TRUE(), 

    CY[Calendar Year]= maxdate,"YTD",

    CY[Calendar Year] = dateadd(maxdate, -1,year),"YTDLY",

    "DEFAULT VALUE"

)

regards

Phil

[dokat]

@PhilipTreacy Thanks for your response. still receiving below error message

The first argument to 'DATEADD' must specify a column.

Below line worked for me

CY[Calendar Year]= maxdate-365,"YTDLY"

.