Forum Discussion

dokat's avatar
dokat
Post Prodigy
4 years ago
Solved

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?

 

CYTierSalesVolume
12/31/2018225125
12/31/2019250200
12/31/20201100500
12/31/20211200600
1/1/20213300700
1/1/20223400800
2/28/20213500900
2/28/202236001000
Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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's avatar
    Samarth_18
    4 years ago

    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's avatar
    dokat
    4 years ago
    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")

13 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

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

      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.

       

      CYTierSalesVolume
      12/31/2018225125
      12/31/2019250200
      12/31/20201100500
      12/31/20211200600
      1/1/20213300700
      1/1/20223400800
      2/28/20213500900
      2/28/202236001000
      YTD310001800

       

       

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        4 years ago

        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")
  • calerof's avatar
    calerof
    Impactful Individual
    4 years ago

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

      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's avatar
        calerof
        Impactful Individual
        4 years ago

        You are missing one closing parenthesis in the first variable.