Forum Discussion
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, ...
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")
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 |
- 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")