Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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"
@montiell Hey mate ,
you have to convert date to date format first as it is a text feild right now .
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 😕
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
Hi AIB,
I tried both and got the same error, Cannot convert value '' of type Text to type Date.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |