Forum Discussion

teatree's avatar
teatree
Frequent Visitor
9 years ago
Solved

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 typ...
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    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's avatar
teatree
Frequent Visitor
9 years ago

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's avatar
v-qiuyu-msft
Community Support
9 years ago

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's avatar
    teatree
    Frequent Visitor
    9 years ago

    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's avatar
      v-qiuyu-msft
      Community Support
      9 years ago

      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