Adjusting SharePoint Created/Modiifed Timestamp columns for TimeZone

[CmdrKeene]

I've got a PowerBI dashboard that is built primarily from a SharePoint list data.

I noticed yesterday I had neglected to account for time zone differences.  A list item was modifed yesterday at 8pm, but it showed as modified at 2am the following date in my dashboard.  I'm in central US time, so -0600 offset from UTC, so it's definitely a timezone issue.

So I went into my query and created a calculated column called "LocalModified" with this formula:

= Table.AddColumn(#"Renamed Columns", "LocalModified", each DateTime.AddZone([Modified],-6))

But it didn't return the results I expected.  I thought that perhaps I should instead just add the time zone component to the original column using this formula instead:

= Table.AddColumn(#"Renamed Columns", "LocalModified", each DateTime.AddZone([Modified],0))

(The only difference is I'm setting the timezone to "0" or UTC). But still not the results I wanted.

But researching I found this gem:

DateTimeZone.ToLocal(DateTime.AddZone([Modified],0)

This appears to work great in PBI desktop, but I hope it still uses central time as "local" when I upload this to the service.

[v-juanli-msft · Accepted solution]

Hi @CmdrKeene 

Please refer to: https://radacad.com/solving-dax-time-zone-issue-in-power-bi

For example, my local time is 2020/2/12 2:55 pm, using DAX:  Now(),

it generate time 2020/2/12 2:55 pm on Power bi Desktop report, but it shows 2020/2/12 6:55 am on Power BI Service report.

My local timezone is +8 for UTC time.

Use dax: 

Measure 2 = NOW()+8/24

or power query

DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),8,0)

finally it shows 2020/2/12 2:55 pm on Power BI Service.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CmdrKeene]

Thanks again, I believe I successfully accomplished this!  Appreciate your help.

[CmdrKeene]

Thanks! I'm going to research the DateTimezone.SwitchZone() function and see about working this into my powerQuery steps.