Sharepoint List adjusted to Date/Time/Timezone & Using Locale showing incorrect time

[virakones]

I have a Sharepoint list that captures Start/End times to jobs.

The times are collected in EST (-04:00) ex. 6/3/2022 8:19 AM EST

When brought into PowerBI they show up as UTC so I have to change it.

From my responses online, it seems the key is to change the column to Date/Time/Time Zone, and change the column to "Use Locale" - in my case, English (Canada).

The column values now show the time with -04:00 in it, ex. 6/3/2022 12:19 PM - 04:00

However, the time reflected in my visuals do not reflect what I would imagine should be 6/3/2022 8:19 AM EST in the above example, it is still showing the UTC time even though I made the changes. 

Is there anything else I'm missing?

[subguts]

Here's a Power Query solution for this.  I think it's quite a bit easier than DAX, but then I have very little DAX knowledge.  Posting here as hoping it will help someone else out as I believe this is a solution to get the correct dates.  At least it worked for my problem and took a lot of time to figure out, so hoping this helps someone else save a LOT of time.  Here goes:

Problem: There is a field that comes across from the data source as the UTC date value, which will have a -4 or -5 attached to it, but no way to adjust to display with the actual time it was entered (in local EST time)

Fix:

1. Change your datetime field to Date/Time/TimeZone.  In my example the field is [Created]
2. Get the hours different from UTC your date time is by adding a new column with this formula:

          = DateTimeZone.ToUtc([Created]))

          Note: I called this field “UTCDifference”

      3. Create a new column to adjust the Created field by the UTC using the following formula to give you the time            that was actually entered into SharePoint:

           = [Created]-#duration(0,[UTCDifference]*-1,0,0))

          Note: I multiply by -1 instead of using an ABS function

          Note:  I called this field “LocalDateTime”

      4. Change the type of the “LocalDateTime” column to be DateTime

          Note: This last step removes the UTC part of the date timestamp. 

Transform Results:

This is what it looks like in Transform. The highlighted steps are the ones corresponding to the ones above.  The other steps were just to rename and remove some of the columns and aren’t necessary for the solution.

Tip:  If this column shows up in the following format when you add it to your Power BI then see below as to how to fix it so it displays as a Date Time field.

You can toggle between having it display in the above hierarchical format and the datetime format by selecting one of the two options under columns.  A screen shot follows here:

End Result:

This is what it looks like in Power BI:

[tanke67]

Just one thing, your column [UTCDifference] must be changed type to "integer". Maybe is kind of obvius, but can save time! 

Thanks! 

[Anonymous · Accepted solution]

Hi @virakones ,

Power BI Service always returns the UTC time. We can't set the time zone on Power BI Service. To get the correct local time, we can add time different in your DAX formula, or use Power Query to get the local time zone datetime. For more details, please see: Solving DAX Time Zone Issue in Power BI.

Best Regards,

Jay

[lbendlin]

Power BI Service runs on UTC.  As long as you keep your timestamps in ISO-8601 format you should be good.

By the way, it's EDT, not EST.

[JamesH111_11]

EST means Eastern Standard Time. EDT means Eastern Daylight Time.

So, it depends on the time of the year that you use the abbreviation. EDT during spring and summer and EST during fall and winter, due to DAYLIGHT savings.