Power BI DirectQuery Date and Time conversion

[rrodillas012]

Hi Good Day! 

I currently creating a dashboard on PowerBI Desktop using DirectQuery and it's connected to Dynamics365 Dataverse, I'm having a hard time figuring out how to convert the date and time from UTC to local time zone (Auckland NZ).
Table name: incident
Date column name: Created On 

I've tried modifying it thru power editor seems like it wasn't allowed in DirectQuery. 

Can someone provide some possible ways on how to convert date and time from UTC to UTC+13?

[Anonymous · Accepted solution]

Hi, @rrodillas012 

Regarding modifying the time zone, here is my example:

1.Currently, the most effective method is to make adjustments through calculated columns.

LocalCreatedOn = IF(ISBLANK('ScanData'[ScanDate]),BLANK(),'ScanData'[ScanDate]+ TIME(13, 0, 0))

2.Additionally, you can use the format() function:

Measure = FORMAT(MAX('ScanData'[ScanDate]),BLANK(), "en-NZ")

However, please note that using the format() function will also alter the original data.

Below is a screenshot of the relevant documentation:

https://learn.microsoft.com/en-us/dax/format-function-dax#datetime-with-optional-locale_name

3.Given that you are in DirectQuery mode, you can also make modifications using SQL statements.

SELECT 
    [ScanData], 
    DATEADD(hour, 13, [ScanData]) AS LocalCreatedOn 
FROM 
    ScanData

4.Furthermore, in Power Query:

[ScanDate]+ #duration(0, 13, 0, 0)

5.Of course, if you are working solely in Desktop, you can make adjustments directly in the settings.

6.You should be aware that, at present, it is not possible to modify the time zone through settings in the service. Here is a link to similar issues:

Solved: timezone corrections on power BI service not the s... - Microsoft Fabric Community

7.Here are the links that might help you:

Solved: Direct Query - transform Date TIme to Date - Microsoft Fabric Community

Solving DAX Time Zone Issue in Power BI - RADACAD

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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