How to Round to Start Hour, DirectQuery!

[Anonymous]

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!

[v-lid-msft · Accepted solution]

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]

Solved

StartHour =

MROUND(

([Data_Hora]

- DIVIDE ( MINUTE ( [Data_Hora] ); 60 * 24; 0 )

- DIVIDE ( SECOND ( [Data_Hora] ); 3600 * 24; 0 )); TIME ( 1; 0; 0 ) ) + TIME ( 0; 0; 0 )

[v-lid-msft]

Hi @Anonymous ,

Glad to hear that you have resolved your problem.

Best regards,

[Anonymous]

It is because time is not only in seconds.

Is it possible to round the date to seconds?

[Anonymous]

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]

Works it, thanks