Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dokat
Post Prodigy
Post Prodigy

Add column with if condition

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")
1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1644811973081.png

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.

View solution in original post

5 REPLIES 5
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1644811973081.png

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.

@v-angzheng-msft It worked. Thanks

amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

@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

dokat_0-1644464788493.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.