Forum Discussion

dokat's avatar
dokat
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Dateadd function returning error

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...
Need Help
  • dokat's avatar
    dokat
    4 years ago

    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")
TheoC's avatar
TheoC
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    dokat
    Icon for Post Prodigy rankPost Prodigy
    4 years ago

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

  • dokat's avatar
    dokat
    Icon for Post Prodigy rankPost Prodigy
    4 years ago

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