Calculated Column not returning correct values

[dokat]

Hi,

I'd like to create a calculated column based on date column. I'd like calculated column measure to return below  values. I have a code below that i tested. However it only returns values for YTD,Last Month, and Last Year. Please see below screenshot of the output, I'd like it to also return corresponding YTDLY, Last MonthLY, and Last YearLY values.

Date Column             Calculated Column

1/31/2022                      YTD

1/31/2021                      YTDLY

1/1/2022                        Last Month

1/1/2021                        Last MonthLY

12/31/2021                    Last Year

12/31/2020                    Last YearLY

Slicer = switch(
true(), 
CY[Calendar Year]= calculate(max(CY[Calendar Year]),all()),"YTD", 
CY[Calendar Year]= calculate(Max('CY'[Calendar Year]),DATESYTD(dateadd('CY'[Calendar Year],-1,Year))),"YTDLY",
CY[Calendar Year]= calculate(Max('CY'[Calendar Year]),DATESMTD(dateadd('CY'[Calendar Year],-1,MONTH))),"Last Month",
CY[Calendar Year]= CALCULATE(Max('CY'[Calendar Year]),DATESMTD(dateadd('CY'[Calendar Year],-12,MONTH))),"Last MonthLY", 
CY[Calendar Year]= DATE ( YEAR (calculate(max(CY[Calendar Year]),all()))-1, 12, 31 ),"Last Year",
CY[Calendar Year]= DATE ( YEAR (calculate(max(CY[Calendar Year]),all()))-2, 12, 31 ),"Last YearLY")

Below you will see the screenshot of the output. It is missing YTDLY, Last MonthLY, and Last YearLY. Appreciate any help to modify the code

I also tried the below if functions to create the measure for calculated column but still getting the same error message.

Slicer = IF(CY[Calendar Year]= calculate(max(CY[Calendar Year]),all()),"YTD", 
IF(CY[Calendar Year]= LASTDATE(DATEADD(CY[Calendar Year],-12,MONTH)),"YTDLY",
IF(CY[Calendar Year]= calculate(Max('CY'[Calendar Year]),DATESMTD(dateadd('CY'[Calendar Year],-1,MONTH))),"Last Month",
IF(CY[Calendar Year]= CALCULATE(Max('CY'[Calendar Year]),DATESMTD(dateadd('CY'[Calendar Year],-12,MONTH))),"Last MonthLY", 
IF(CY[Calendar Year]= DATE ( YEAR (calculate(max(CY[Calendar Year]),all()))-1, 12, 31 ),"Last Year",
IF(CY[Calendar Year]= DATE ( YEAR (calculate(max(CY[Calendar Year]),all()))-2, 12, 31 ),"Last YearLY")

)))))

[dokat · Accepted solution]

I modified the code slightly, 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")