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")
calerof Thank you for your response. However formula returned error on my end. Please see below screen shot and i'd like to have last year, year to date and last month variables on the column as it will be used as a slicer for date selection.
Latest month in this case 2/28/2022 needs to be "last month" in the new column
2021 needs to be "last year",, and year to date "ytd"
Error Message
You are missing one closing parenthesis in the first variable.
calerof Thank you for your reply, ultimately i'd like new column to look like below.
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 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")