Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |