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 |