the Auto Date/Time time intellgence feature is turned on but no date hierarchy auto created

[vika160]

I was going thru a lot of reading/ viewing videos and I cannot figure this out.

the time intelligence feature in setting in Power BI Desktop is turned on but there is no hierarchy created on any date fields.

Is there anything else that I am missing? 

Thank you 

[az38]

Hi @vika160 

are you sure your date columns have a date type in Power Query?

[vika160]

yes.  The data is coming from sql server. the fields are date only format. I tried to change few to to date/time format - it is the same.

when I drop a field on a cansas, it shows date format

[az38]

@vika160 

you can share your pbix-file with dummy data to cloud service and we have a look

[vika160]

how I can do this?

[vika160]

Sorry, my bad.

yes, the date fields I was interested in are not date type in BI.

That would rise another question - why not?

they  casted as Date in SQL view

I did not notice it because when I do a data transformation in Power query, they sare shown as Date data type but after loading intopower BI they are not.

[az38]

@vika160 

usually, the issue in regional settings difference. try to set this column as date in power query, if it has apptopriate date format it will be detected good

[vika160]

 columns show the Date type in Power query. I reselected them again as date type.

= Table.TransformColumnTypes(#"Removed Columns",{{"StatusDate", type date}, {"StatusChangeDate", type date}, {"Opened_Date", type date}, {"Closed_Date", type date}, {"ReportedDate", type date}})

they are not date type in BI after import.

Opened Transfromation and they are shown as Date type.

Any ideas??

thank you

[az38]

@vika160 

sorry, I need to have a look at your pbix

[vika160]

here is the link: 

https://www.dropbox.com/s/qkxzvk6015e1tcd/test1.pbix?dl=0

thanks

[vika160 · Accepted solution]

Thanks for the help.

I figured this out.

I was using DirectQuery feature from SQl server, not an Import.

It looks like this feature has some limitations on how it handles dates.

Even if it is shown in Power Quesry as Date type and it allows to apply Date data type, it still presenting data in BI as it is stored  in SQL server table. 

I am not sure would htis be solved at some time or not.

[az38]

@vika160 

go to cloud filw sharing resource like https://uploadfiles.io/ , upload your pbix and share link here