Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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:
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.
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:
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.
Thanks! I'm going to research the DateTimezone.SwitchZone() function and see about working this into my powerQuery steps.