Forum Discussion
if condition doesn't work with date column
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 |
Anonymous , 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")Anonymous 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")- Slightly modified the data table and below is working for meNew 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")
13 Replies
Samarth_18Community Champion
Hi Anonymous ,
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
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
Anonymous , 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")
- calerof
Impactful Individual
Hi Anonymous ,
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
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.
