Confusion about DateTime.[Date]

[kongyuancn]

Hi All,

I'm confused about DateTime.[Date]

I have a small table:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU31jcyMDJUMDC0AiMFR1+lWJ1oJTN9U6iEkRUYwSTM9Q1hWoytwAiLjIkVGIFlYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}})
in
#"Changed Type"

It looks like:

In PowerBi I created an calculated column:

Date = [DateTime].[Date]

Now In PowerBi it created a column converting datetime to date:

 I then created another table:

Table 2 = VALUES('Table'[Date])

It looks good.

I then create another table:

Table 3 = VALUES('Table'[DateTime].[Date])

I thought Table 2 and Table 3 should be same. But I'm wrong:

Can anyone help me to understand why?

[Anonymous]

IT's not wrong by any means. The hidden tables that underlie the date hierarchy feature (auto-datetime) have all dates regardless of what's in your tables. Hence you see a full list of consecutive dates when you use 'T'[Date].[Date]. It's all by design. However, I warn you that you should never - I mean NEVER - use the auto-generated dates. It'll sooner rather than later drive you crazy and will make you cry. Not to mention that this feature is there for the total newbies who don't know how to create good dimensional models.

Beware of the auto-generated date hierarchies. Instead, do it the right way: create a calendar on your own. You'll thank me later.

[Preeti_Yadav]

Hi @kongyuancn ,
You might try to change the data type from the above ribbon given in the report view,from the option data type.

It might help!!

Thanks !!

Preeti