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