Forum Discussion

montiell's avatar
montiell
New Member
5 years ago

Extract date from a column with date time

Hello team,

 

I am practically still new with power bi dax formulas and I am trying to accomplish to create a new colum or date hirearchy. My current colum data is format as follow:

 

11/26/2020 5:03:41 AM EST


I would like to create a column with mm/dd/yyyy but i cant figured it out. I tried this dax formula:

 

Completed-MonthYear = FORMAT([DATACOLUMN],"m/dd/yyyy") but it didnt work, I got the erros message:
 
Cannot convert value '' of type Text to type Date. I tried converting the date from DATACOLUM into date but i got the same error msg.
 
Any help or guidence is really appreciated!
 
Thanks and Happy holidays!
 
Luis F Montiel

6 Replies

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    5 years ago

    montiell ,

     

    Try this mcode:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUNzLTNzIwMlAwtTIwtjIxVHD0VXANDlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Column1.1", type datetimezone}}, "en-US"),
    #"Inserted Date" = Table.AddColumn(#"Changed Type with Locale", "Date", each DateTime.Date([Column1.1]), type date)
in
    #"Inserted Date"
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    montiell  Hey mate ,
    you have to convert date to date format first as it is a text feild right now .

    "Completed-MonthYear = FORMAT([DATACOLUMN],"m/dd/yyyy") but it didnt work, I got the erros message:
     
    Cannot convert value '' of type Text to type Date. I tried converting the date from DATACOLUM into date but i got the same error msg."

    first convert /format it is as a date coloum then use your formula it will work.
     
    Completed-MonthYear = FORMAT([DATACOLUMN],"m/dd/yyyy")
     
     
    it will work thank you 
    • montiell's avatar
      montiell
      New Member
      5 years ago

      I think you are right, but Everytime i tried to converted i receive the following msg:

       

       

      I think is because my date include the time zone:

       

       

      and after the error of the formula this appears on my calculated column:

       

       

      I think If i can resolve the issue with the data type and be able to converted as date it will get fix, sadly i keep receiving the same error everytime 😕

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi montiell 

    Completed-MonthYear = DATEVALUE( [DATACOLUMN] )

    or

    Completed-MonthYear = INT( [DATACOLUMN] )

     then convert the column to date  and choose the format you prefer

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

  • montiell's avatar
    montiell
    New Member
    5 years ago

    Hi AIB,

     

    I tried both and got the same error, Cannot convert value '' of type Text to type Date.

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    montiell 

    It should work. You're probably doing something incorrectly. See it all at work in the attached file. Pay particular attention to the data types

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers