Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Stop PBI from converting to UTC time

My date/time field is in EST in my SQL database but PBI switches it into UTC. I want it in EST, anyway to just keep it EST from the start instead of converting back and forth?

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

It is not supported yet currently. There is an idea about that you can vote it up to make this feature coming sooner.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/35610880-fix-releative-date-slicer-utc-issue

 

BTW, you can create a custom column using DateTime.AddZone function to work around. This function adds the timezonehours as an offset to the input datetime value and returns a new datetimezone value.

DateTime.AddZone([CreatedOn],3)

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

It is not supported yet currently. There is an idea about that you can vote it up to make this feature coming sooner.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/35610880-fix-releative-date-slicer-utc-issue

 

BTW, you can create a custom column using DateTime.AddZone function to work around. This function adds the timezonehours as an offset to the input datetime value and returns a new datetimezone value.

DateTime.AddZone([CreatedOn],3)

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

This is old, but still relevant, so I'll post this for anyone else who is still having this issue.

 

This relates to specifically to SQL server as the data source.

 

Essentially, you need to explicitly convert UTC times to your local timezone as part of the query for your table. It needs to be done for each date/time column. If you do not explicitly make the conversion, Power BI will display the dates in UTC time when uploaded to Power BI service.

 

The easiest way to do this is like this (change ParentBatchCreatedAt to your column name and obviously set your correct time zone):

 

CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, ParentBatchCreatedAt), DATEPART(TZOFFSET,ParentBatchCreatedAt AT TIME ZONE 'New Zealand Standard Time'))) AS ParentBatchCreatedAt

 

Note: ParentBatchCreatedAt AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' does not work properly and will not help. It needs to be the one above.

 

Doing this lets Power BI know that an explicit conversion has been set on the column and to not touch it.

 

Longer example of query:

 

SELECT
DFtriggerType,
DFTriggerName,
ParentBatchID,
BatchDesc,
CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, ParentBatchCreatedAt), DATEPART(TZOFFSET,ParentBatchCreatedAt AT TIME ZONE 'New Zealand Standard Time'))) AS ParentBatchCreatedAt,
CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, ParentBatchClosedAt), DATEPART(TZOFFSET,ParentBatchClosedAt AT TIME ZONE 'New Zealand Standard Time'))) AS ParentBatchClosedAt,
BatchID,
CurrentLoadStatus,
ProcessName
FROM [ETL].[BatchDataLoadDetail]

 

If your dates are already converted in the source to your local time (not good practice when the server is on a different time zone i.e. UTC), you will need to convert the dates to UTC and then to your local time zone. I'd probably use a view to convert the dates to UTC and then in Power BI pull from that view with the local time zone conversion as above.

Hope this helps.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.