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

[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]

Hi @rrodillas012 ,

Please try this

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

Best Regards,

Wearsky

[FreemanZ · Accepted solution]

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.