Forum Discussion
Short Dates - Missing Data
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
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
6 Replies
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) )
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?
teatree you should do that once data from sql server is imported into power bi desktop and do that via modellig tab.
