Change timezone of directquery dates

[icbd]

I am running a report off of an SQL database that must remain in directquery mode, and is populated by proprietary software that I cannot change.

All datetime is devoid of timezone, so PowerBI defaults to +00:00, which is UTC time. I need all dates to be on EST time zones for relative filters to work.

So far, I've tried:

• Changing the column format to Date/Time/Timezone and doing a find and replace on the +00:00 text. Does not work
• Changing the column format to Date/Time/Timezone and adding a custom column using DateTimeZone.SwitchZone. Does not work either, turns table into import mode (it is too large to be imported and will only get larger with time).
• Tried DAX measure : Now()-(5/24), does not work as it cannot be used as a page filter nor a relative filter.

Any other suggestions? I find it completely stupid, bordering on useless, to not be able to set timezones on reports or use the end user's computer's timezone. Power BI is now 7 years old, surely this shouldn't be this hard to implement?

[mahoneypat]

I did a simple test with a published Power BI datsets and converting it to a local model (DQ mode).  I was able to add a DAX column with this formula to adjust all the datetime values to 5 hrs earlier.  Would the same work for you?

DT Adj = Data[DT] - TIME(5,0,0)

Pat

[StefanoGrimaldi]

he tried, but he wants to be able to use it for a filter also, and as a measure you cant use it for filtering, also he told dual mode DQ/Import wasnt a option for him, so thats why I suggested him to do a direct sql statement when calling the DQ source, cant think of a work around other tham that cause those 2 limits he have there. 

[StefanoGrimaldi]

use a sql statement like this on the query call for the direct querye sql: 

declare @utc_date datetime = getdate()select @utc_date as utc_time_zone,   dateadd(hh, datediff(hh, getutcdate(), getdate()), @utc_date) as local_time_zone

like this you can send directly to the server a query that change that column from utc to a specific time zone

[icbd]

Good morning,

I've not been able to work on this until just now

So, I've been able to get to the point where I can enter the SQL statement.

As of right now, I have 6 different columns, spread over 4 tables. Current format of all those date/times are as follows (this is copied straight from the SQL database)

2020-06-02 13:16:30.0000000

The table names and columns where I'd need the timezone info are as follows:

Table name	Column name
DataPartCell1	EndTime
OeeDefectHistory	Time
OeeFaultHistory	StartTime
OeeFaultHistory	StopTime
OeeModeHistory	StartDate
OeeModeHistory	StopDate

I am not super well versed in SQL, is there a query that would allow me to add the timezone info in my columns as they get pulled into PowerBI? Would that cause any data refresh slowdown?

I appreciate all the help you've given me so far.

[v-rzhou-msft]

Hi @icbd 

You may refer to blogs as below, I hope they can help you solve your problem.

For reference:

Power BI Convert UTC to Local Time

Timezone conversion in Direct Query

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[icbd]

How do I get to that page once the data source has already been setup? I can't seem to get there.

Edit: I got there, but the SQL statement box is greyed out.

[StefanoGrimaldi]

try setting the model to a dual mode : direct query and import mode, this should let you change the column type by region , also can try setting a sql statement on the source call to do the server manage the time zone changing, 

[icbd]

I'm not sure what you mean by dual mode. The model is already a mixed model as I have a few automatically generated tables to apply different filters to the report.

The dataset is much, much larger than the datacap offered by PowerBI and growing larger by the minute. Importing the table is not an option. Thus it must remain in directquery.