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