Forum Discussion
if condition doesn't work with date column
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 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")
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 |
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 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.
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")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.
CY Tier Sales Volume New Column 12/31/2018 2 25 125 12/31/2019 2 50 200 12/31/2020 1 100 500 12/31/2021 1 200 600 Last Year 1/1/2021 3 300 700 1/1/2022 3 400 800 2/28/2021 3 500 900 2/1/2022 3 600 1000 Last Month 2/28/2022 3 1000 1800 YTD
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")