Incorrect time - Console log tableDataView

[AdamWhittaker]

Hello,

I am trying to correctly display datetimes in a custom visual but the time is always off.

I am going from Salesforce > MS SQL > Power BI > Customer Visual

The time comes out of Salesforce in UTC, then in SQL Server it looks like this  (which is still in UTC - my local time at the time was 11:56):

SQL Server DataTime: 2024-10-25 15:56:00.000
SQL Server DateTimeOffset: 2024-10-25 15:56:00.0000000 +00:00

In a table visual (which looks good for UTC):
In a table visual: Fri, 25 Oct 2024 15:56:00

But then logging the tableDataView it shows this:
Console log (SQL Server DataTime): "2024-10-25T19:56:00.000Z"
Console log (SQL Server DateTimeOffset): "2024-10-25T19:56:00.000Z"

Why would it jump 4 hours ahead? If it was going to change it I would have assumed it would change it to local time which would have been -4 hours.

I tried both building the visual and testing local time to avoid any issues with local testing and the PBI service but both locally and debugging on the service have the same times.

Any ideas?

Thanks,

[AdamWhittaker]

I guess my only option is to bring them through as strings and let JS do the conversion?

[lbendlin]

Can you create a view in your SQL Server that presents these timestamps in the proper ISO-8601 format?

[AdamWhittaker]

Maybe, what does power bi want though? Power bi itself shows the correct time (if i put it in a table or matrix etc its good), its only converting the date/time (+4 hours) when being sent to a custom visual and its not the visual since i am logging the datatable before i even touch it in there? Is there something in the pbiviz code doing the change? (which is why i was thinking just sending it as a string would be easier, but i am still curious to why its changing it)

[lbendlin]

The problem is that Power Query understands timezones but Power BI does not. On top of that the Power BI service is running on UTC.

It's a very old  (pardon the pun) problem.  Solving DAX Time Zone Issue in Power BI - RADACAD

[lbendlin]

Instead of datetime use datetimezone in Power Query.

[AdamWhittaker]

I am in direct query mode and it won't allow that: