Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to Round to Start Hour, DirectQuery!

Dear all,

I need to round current time to the start hour, but i'm working in direct query.

When i try do this on "edit query" tell me that's not supported in direct query mode.

I need to work whith direct query, i cant export all data.

It is possible to do it in another way?

Thanks!

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can create a calculated column to meet your requirement:

     

    StartHour = 
[DateTime]
    - DIVIDE ( MINUTE ( [DateTime] ), 60 * 24, 0 )
    - DIVIDE ( SECOND ( [DateTime] ), 3600 * 24, 0 )

     

     


    Best regards,

     

6 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can create a calculated column to meet your requirement:

     

    StartHour = 
[DateTime]
    - DIVIDE ( MINUTE ( [DateTime] ), 60 * 24, 0 )
    - DIVIDE ( SECOND ( [DateTime] ), 3600 * 24, 0 )

     

     


    Best regards,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Works it, thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      One more thing you do that with new mesure or new column?

      I only can do it with a new column, but i noticed when i try to count all values they give me wrong results

       

       

      I have values ​​in the order of tens and should have in the thousands.

      Try to use relationships with the calendar, but get the same results.

      Thank you

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      It is because time is not only in seconds.

       

      Is it possible to round the date to seconds?