Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
2 years ago
Solved

Converting column to YY-MM format

I'd like to create an additional column (like a copy of this column) but in the format YY-MM) but I'm getting stuck with it having TBD in it.  How could I have the column:

 

If blank - leave blank

If TBD - leave TBD

if it's a date - then give me format YY-MM

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jcastr02 ,

    Please try:

    Column2 = 
SWITCH(
    TRUE(),
    ISBLANK('Table'[Column1]),BLANK(),
    'Table'[Column1]="TBD","TBD",
    NOT ISERROR(DATEVALUE('Table'[Column1])),FORMAT(DATEVALUE('Table'[Column1]),"yy-MM") 
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    jcastr02 , Try like

     

    New column in Dax

     

    Switch( True(),

    isblank([Col1]) || [Col1] ="" || [Col1] = " ", blank(),

    [Col1] ="TBD" , "TBD",

    format(Datevalue([Col1]), "YYMM") )

     

    You have to handle all no datevalues

     

     

    You can try following in power query

     

    = try Text.From(Date.From([Col1])) otherwise Text.From([Col1])

    • jcastr02's avatar
      jcastr02
      Post Prodigy
      2 years ago

      Hello amitchandak  I tried that but seems that it's not taking the DAX do you know what I'm droing incorrect here?  Thank you. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jcastr02 ,

    Please try:

    Column2 = 
SWITCH(
    TRUE(),
    ISBLANK('Table'[Column1]),BLANK(),
    'Table'[Column1]="TBD","TBD",
    NOT ISERROR(DATEVALUE('Table'[Column1])),FORMAT(DATEVALUE('Table'[Column1]),"yy-MM") 
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum