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

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