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

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'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",
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
      Post Prodigy
      4 years ago

      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.

       

      CYTierSalesVolumeNew Column
      12/31/2018225125 
      12/31/2019250200 
      12/31/20201100500 
      12/31/20211200600Last Year
      1/1/20213300700 
      1/1/20223400800 
      2/28/20213500900 
      2/1/202236001000Last Month
      2/28/2022310001800YTD
      • dokat's avatar
        dokat
        Post Prodigy
        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")
    • dokat's avatar
      dokat
      Post Prodigy
      4 years ago

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