Format Date_Time

[Khomotjo]

Morning I have a date_time field and I want to  only the date Field. This is the original data : 

This is the table when I format and select short date , The issue is that Power Bi still considers the time and hence I have duplicate records of the same date, I want only 1 record for each of the dates.

 I tried  

Finalised_Date = DATE(YEAR(StockMovements[FinalisedDate]),MONTH(StockMovements[FinalisedDate]),DAY(StockMovements[FinalisedDate])) but got error :  

[danextian · Accepted solution]

You are seeing multiple dates because you simply changed the display or formatting but not the actual data type. Several methods to extract the date:

[Anonymous · Accepted solution]

Hi @Khomotjo ,

Thanks to danextian  and uzuntasgokberk  for their quick answers. I have some other thoughts to add:
(1) Here is my test data.

(2) Create a calculation table. Select the Date column to change the data type.

Table =
ADDCOLUMNS (
    SUMMARIZE ( 'Table_3', [FinalisedDate], [id] ),
    "date",
        DATE ( YEAR ( [FinalisedDate] ), MONTH ( [FinalisedDate] ), DAY ( [FinalisedDate] ) )
)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[danextian · Accepted solution]

You are seeing multiple dates because you simply changed the display or formatting but not the actual data type. Several methods to extract the date:

[uzuntasgokberk]

Hello @Khomotjo ,

Could you please try that navigate to Power Query(Transform Data), select Finalised_Date, Change Data Type to "Date/Time", Change Data Type "Date" and remove duplicates. This way is more performance and effective way.

Here is the example:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE3MNc3MlMwNLMyNrcyMFOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Time" = _t]),
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"Date Time", type datetime}}),
  #"Changed column type 1" = Table.TransformColumnTypes(#"Changed column type", {{"Date Time", type date}}),
  #"Removed duplicates" = Table.Distinct(#"Changed column type 1", {"Date Time"})
in
  #"Removed duplicates"

Kind Regards,
Gökberk Uzuntaş

📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |

[Khomotjo]

Thanks @uzuntasgokberk  I forgot to mention that this is a direct query in power BI. Also  each line  represent a unique order line and the exact time these orders were finalised.  I am interested in the day and not necesarily when(time) the  specific order line was finalised.

[uzuntasgokberk]

Hello @Khomotjo ,

Okey, so you'd like to see only day values.Could you please try new calculated column(how to create calculated column: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-calculated-columns) to your table and write below the dax code:
" Day Column = DAY('YourTable'[YourDate])"

And I'd like to mention that this error indicates that there is a circular dependency in the data model, first we can first check the relationship between the tables in the whole data model, if a table references itself directly or indirectly through other tables, a circular reference occurs.(https://community.fabric.microsoft.com/t5/Report-Server/COULDN-T-LOAD-THE-DATA-FOR-THIS-VISUAL-OLE-D...)

Kind Regards,
Gökberk Uzuntaş

📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |