topic PowerBI DirectQuery, Date and Time conversion from UTC to UTC+13 in DAX Commands and Tips

PowerBI DirectQuery, Date and Time conversion from UTC to UTC+13

rrodillas012 — Wed, 23 Oct 2024 23:03:59 GMT

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?

Re: PowerBI DirectQuery, Date and Time conversion from UTC to UTC+13

FreemanZ — Thu, 24 Oct 2024 00:35:10 GMT

hi rrodillas012 ,

try to

1) create a calculated date table referencing your original datetime column,

2) shift 13h with a new date column

3) add the year, month column based on the shifted date column.

Re: PowerBI DirectQuery, Date and Time conversion from UTC to UTC+13

Anonymous — Thu, 24 Oct 2024 03:25:12 GMT

Hi rrodillas012 ,

Please try this

NewTable= Table.TransformColumns(dbo_cr6d7_table14, {"cr6d7_datecolumn", each _ + #duration(0, 13, 0, 0)})

Best Regards,

Wearsky