topic Re: Extract date from a column with date time in DAX Commands and Tips

Extract date from a column with date time

montiell — Thu, 26 Nov 2020 18:46:56 GMT

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

Re: Extract date from a column with date time

AlB — Thu, 26 Nov 2020 18:58:54 GMT

Hi montiell

Completed-MonthYear = DATEVALUE( [DATACOLUMN] )

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

Re: Extract date from a column with date time

montiell — Thu, 26 Nov 2020 19:07:41 GMT

Hi AIB,

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

Re: Extract date from a column with date time

AlB — Thu, 26 Nov 2020 21:42:59 GMT

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

Re: Extract date from a column with date time

Anonymous — Fri, 27 Nov 2020 16:36:21 GMT

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

Re: Extract date from a column with date time

montiell — Fri, 27 Nov 2020 16:45:04 GMT

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 😕

Re: Extract date from a column with date time

camargos88 — Sat, 28 Nov 2020 03:06:54 GMT

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"