Extract date from a column with date time

[montiell]

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

[camargos88]

@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]

@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]

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]

@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 

[montiell]

Hi AIB,

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

[AlB]

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