Short Dates - Missing Data

[teatree]

Hi,

I have a list of short dates where day/months weren't recorded before May 2016.

e.g. 

2012

2013

2014

2015

2016

05/05/2016

08/08/2016

..etc

I'm unable to change the data type, so these values are just text. 

I would like to get a summary of 2016 as a whole compared to 2014, but then also be able to drill down into 2016. I know that I did this previously but I'm not sure how. I think I used a measure? 

Any help would be appreciated!

Thank you,

Francesca

[ankitpatira]

@teatree You can create calculated column using DAX as below to get 01/01 appended to rows where date is missing and then convert type of that column to date.

= IF( LEN(TABLE(column)) = 4, "01/01/" & TABLE(column), TABLE(column) )

[teatree]

@ankitpatira thank you for your response.

I'm using power bi desktop connected to a SQL server so am unable to convert the type. Am I doing something wrong?

[v-qiuyu-msft · Accepted solution]

Hi @teatree,

As you retrieve data from SQL Server database, it depends on which type you closed to connect. If you connect to SQL Server database in Import mode, then you can create a calculated column then change data type to Date mentioned by @ankitpatira. And create a column chart, place this calculated column in Axis property, drill down feature is enabled. See:

If you connect to SQL Server in DirectQuery mode, after you create a calculated column then change data type to Date, and place it in Axis property, it will not generate date hierarchy. We need to create columns about year, month and day separately, then place those columns in Axis property. See:

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[teatree]

hi @v-qiuyu-msft

thank you for your help. 

my problem is when you said "in directquery mode, after you create a calculated column then changed data type to date"

this doesn't seem to work in direct query. i get this message:

[v-qiuyu-msft]

Hi @teatree,

In my example, I added a calculated column in report not in Query Editor. Please go through my post again and try in your environment.

Best Regards,
Qiuyun Yu

[ankitpatira]

@teatree you should do that once data from sql server is imported into power bi desktop and do that via modellig tab.