Forum Discussion

dokat's avatar
dokat
Post Prodigy
4 years ago
Solved

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")
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

5 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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's avatar
      dokat
      Post Prodigy
      4 years ago

      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's avatar
      dokat
      Post Prodigy
      4 years ago

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