Add column with if condition

[dokat]

Hi,

I have a table (Tier) where i'd like to add column based on CY Column with Month End Dates. I am using below formula if date is 1/31/2021 add Jan, if 2/28/2021 add in February....and YTD add YTD.  However formula is not working can nayone help me with what i am doing wrong?

New Column = switch(selectedvalue('Tier'[CY]),1/31/2021,"Jan",2/28/2021,"Feb",3/31/2021,"Mar",4/30/2021,4,"Apr",5/31/2021,"May",6/30/2021,"Jun",7/31/2021,"Jul",8/31/2021,"Aug",9/30/2021,"Sep",10/31/2021,"Oct",11/30/2021,"Nov",12/31/2021,"Dec","YTD","YTD")

[v-angzheng-msft · Accepted solution]

Hi, @dokat 

Column =
SWITCH(
    TRUE(),
    [CY] = dt"2018-12-31", "2018",
    [CY] = dt"2017-12-31", "2017",
    [CY] = dt"2019-12-31", "2019",
    [CY] = dt"2020-12-31", "2020",
    [CY] = dt"2021-12-31", "last year",
    [CY] = dt"2022-02-28", "last month"
)

Result:

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dokat]

@v-angzheng-msft It worked. Thanks

[amitchandak]

@dokat , You are trying to do two thing here.

New column

Month Name = format([Date], "mmm")

YTD for this year as column

YTD = if([Date] >= date(year([Date]),1,1) && [Date] <= today() , "YTD", "Not YTD" )

Also refer

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[dokat]

@amitchandak  I am using below formula toc aptire last year but it looks up for 1/1/2021 and not 12/31/2021. Is there a way to modify where it looks up last date of the year? Thanks

if(('Tier'[CY])=DATEADD('Tier'[CY],-1,Year),"Last Year"))

[dokat]

@amitchandak formula returned not right values

Ultimately 'Tier'[CY] = 12/31/2021 then return "last year" in the new colun

If 'Tier'[CY] = 12/31/2021 return last year

if 'Tier'[CY] = 2/28/2022 return last month

if 'Tier'[CY] =12/31/2018 return 2018

if 'Tier'[CY] =12/31/2017 return 2017

if 'Tier'[CY] =12/31/2019 return 2019

if 'Tier'[CY] =12/31/2020 return 2020

Please see below screen shot fo rthe results of the formula. Thanks