if condition doesn't work with date column

[dokat]

Hi

I have a table(Tier) below where i'd like to add a column based on a condition on CY. 

If ('Tier'[CY])=Max('Tier'[CY]), "YTD",

Tier'[CY])=DATE ( YEAR ( SELECTEDVALUE ('Tier'[CY]))-1, 12, 31 )='Tier'[CY],"Last Year"

'Tier'[CY])=DATE ( YEAR ( SELECTEDVALUE ('Tier'[CY]))-2, 12, 31 )='Tier'[CY],"2020")

('Tier'[CY])=DATE ( YEAR ( SELECTEDVALUE ('Tier'[CY]))-3, 12, 31 )='Tier'[CY],"2019")

('Tier'[CY])=DATE ( YEAR ( SELECTEDVALUE ('Tier'[CY]))-4, 12, 31 )='Tier'[CY],"2018")

Formula works for the first condition however returns blank for all other conditons. This is the error message i am receiving

"DAX comparison operations do not support comparing values of type True/False with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values."

Can anyone advise on why the formula is not working?

CY	Tier	Sales	Volume
12/31/2018	2	25	125
12/31/2019	2	50	200
12/31/2020	1	100	500
12/31/2021	1	200	600
1/1/2021	3	300	700
1/1/2022	3	400	800
2/28/2021	3	500	900
2/28/2022	3	600	1000

[Samarth_18 · Accepted solution]

@dokat , Try this:-

Column 2 = 
var max_date = calculate(max(Tier[CY]),all())
return

switch(true(),
Tier[CY]= max_date,"YTD",
MONTH(Tier[CY]) = month(max_date),"Last Month",
Tier[CY]= DATE ( YEAR (max_date)-1, 12, 31 ),"Last Year")

[Samarth_18 · Accepted solution]

@dokat  Try this

Column 2 = 
var max_date = calculate(max(Tier[CY]),all())
return

switch(true(),
Tier[CY]= max_date,"YTD",
and(MONTH(Tier[CY]) = month(max_date),YEAR(Tier[CY]) = YEAR(max_date)),"Last Month",
Tier[CY]= DATE ( YEAR (max_date)-1, 12, 31 ),"Last Year")

[calerof]

Hi @dokat ,

You can use this code:

Year Selected = 
VAR YearSelected = YEAR(MAX(Table_Tier[CY]))
VAR CurrentYear = YEAR(TODAY())
RETURN
SWITCH(
    TRUE(),
    YearSelected = CurrentYear, "YTD",
    YearSelected
)

Hope it helps.

Regards,

Fernando

[dokat]

@calerof Thank you for your response. However formula returned error on my end. Please see below screen shot and i'd like to have last year, year to date and last month variables on the column as it will be used as a slicer for date selection.

Latest month in this case 2/28/2022 needs to be "last month" in the new column

2021 needs to be "last year",, and year to date "ytd"

Error Message

[calerof]

You are missing one closing parenthesis in the first variable.

[dokat]

@calerof  Thank you for your reply, ultimately i'd like new column to look like below. 

CY	Tier	Sales	Volume	New Column
12/31/2018	2	25	125
12/31/2019	2	50	200
12/31/2020	1	100	500
12/31/2021	1	200	600	Last Year
1/1/2021	3	300	700
1/1/2022	3	400	800
2/28/2021	3	500	900
2/1/2022	3	600	1000	Last Month
2/28/2022	3	1000	1800	YTD

New Column = var max_date = calculate(max(Tier[CY]),all()) return switch(true(), Tier[CY]= max_date,"YTD", and(MONTH(Tier[CY]) = month(max_date),YEAR(Tier[CY]) = YEAR(max_date)),"Last Month", Tier[CY]= DATE ( YEAR (max_date)-1, 12, 31 ),"Last Year")

[Samarth_18]

Hi @dokat ,

Below code would be ideal code based on what you have tried:-

Column =
VAR max_date =
    CALCULATE ( MAX ( Tier[CY] ), ALL () )
RETURN
    SWITCH (
        TRUE (),
        Tier[CY]
            = DATE ( YEAR ( max_date ) - 1, 12, 31 ), "Last Year",
        Tier[CY]
            = DATE ( YEAR ( max_date ) - 2, 12, 31 ), "2020",
        Tier[CY]
            = DATE ( YEAR ( max_date ) - 3, 12, 31 ), "2019",
        Tier[CY]
            = DATE ( YEAR ( max_date ) - 4, 12, 31 ), "2018"
    )

Output:-

Rest of the column will remain blank since we are comaparing only 12/31 of the year.

Thanks,

Samarth

[dokat]

@Samarth_18 Thanks for your response. Actually i am going to use this column for a slicer so i will need to have latest month and year to date selections. How can i add latest month as last month and YTD as YTD? So when i select the YTD, last month or last year on slicer calculations will change.

CY	Tier	Sales	Volume
12/31/2018	2	25	125
12/31/2019	2	50	200
12/31/2020	1	100	500
12/31/2021	1	200	600
1/1/2021	3	300	700
1/1/2022	3	400	800
2/28/2021	3	500	900
2/28/2022	3	600	1000
YTD	3	1000	1800

[Samarth_18 · Accepted solution]

@dokat , Try this:-

Column 2 = 
var max_date = calculate(max(Tier[CY]),all())
return

switch(true(),
Tier[CY]= max_date,"YTD",
MONTH(Tier[CY]) = month(max_date),"Last Month",
Tier[CY]= DATE ( YEAR (max_date)-1, 12, 31 ),"Last Year")

[dokat]

@Samarth_18  Thank you for the code. How can i add  "Last MonthLY", "YTDLY"  and "Last YearLY" I tried to modify as below but it didnt work.

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

Below is the screenshot of what code returns

I'd like 1/31/2021 to return "YTDLY. Is there anyway to do this?

           1/1/2021 to "Last MonthLY"

           12/31/2020 to "Last YearLY"

var max_date = calculate(max(Tier[CY]),all())
return

switch(true(),
Tier[CY]= max_date,"YTD",
and(MONTH(Tier[CY]) = month(max_date),YEAR(Tier[CY]) = YEAR(max_date)),"Last Month",
Tier[CY]= DATE ( YEAR (max_date)-1, 12, 31 ),"Last Year")

[dokat]

@Samarth_18  Actually i noticed last month value is not correct. It's returning 2/28/2021 and not 2/28/2021. How can i modify the formula so that it read latest month in 2022.

[Samarth_18 · Accepted solution]

@dokat  Try this

Column 2 = 
var max_date = calculate(max(Tier[CY]),all())
return

switch(true(),
Tier[CY]= max_date,"YTD",
and(MONTH(Tier[CY]) = month(max_date),YEAR(Tier[CY]) = YEAR(max_date)),"Last Month",
Tier[CY]= DATE ( YEAR (max_date)-1, 12, 31 ),"Last Year")

[dokat]

@Samarth I modified your formula below to capture last month however it still didnt work.

New Column = var max_date = calculate(max(Tier[CY]),all())

var First_date = STARTOFMONTH('Tier'[CY])

return switch(true(),

Tier[CY]= max_date,"YTD",

and(MONTH(Tier[CY]) = month(max_date && First_date),"Last Month",

Tier[CY]= DATE ( YEAR (max_date)-1, 12, 31 ),"Last Year")

)

[dokat]

@Samarth_18 this one didnt return anything for Lats month, it was blank. Ultimately below is what i am trying new column too look like. I hope this helps clarify.

CY	Tier	Sales	Volume	New Column
12/31/2018	2	25	125
12/31/2019	2	50	200
12/31/2020	1	100	500
12/31/2021	1	200	600	Last Year
1/1/2021	3	300	700
1/1/2022	3	400	800
2/28/2021	3	500	900
2/1/2022	3	600	1000	Last Month
2/28/2022	3	1000	1800	YTD

[dokat · Accepted solution]

Slightly modified the data table and below is working for me

New Column = var max_date = calculate(max(Tier[CY]),all()) return switch(true(),

Tier[CY]= max_date,"YTD",

and(MONTH(Tier[CY]) = month(max_date),YEAR(Tier[CY]) = YEAR(max_date)),"Last Month",

Tier[CY]= DATE ( YEAR (max_date)-1, 12, 31 ),"Last Year")