Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CmdrKeene
Helper IV
Helper IV

Adjusting SharePoint Created/Modiifed Timestamp columns for TimeZone

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.


CmdrKeene
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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.

 

 

 

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

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.

 

 

 

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.

 


CmdrKeene

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors