Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Need Help! Converting UTC to local time (using Direct Query storage mode)

  • 1.  Need Help! Converting UTC to local time (using Direct Query storage mode)

    Posted Jul 10, 2018 11:42 AM
    Hello, all!

    Here's my situation. I have a series of reports that will be consumed by users worldwide. The database is spitting out datetime fields (Start DateTime, End DateTime, etc...) in UTC.

    For the best user experience possible, I want to convert these dates into local time for users, regardless of their location across the globe.  I would love to be able to have an automatic, built-in conversion for UTC-to-local time for users, so their experience is seamless. Full discloser, the data storage mode in these reports is Direct Query, which can and does change the game for some possible solutions, as you all know. One more thing... users will be accessing reports via Row Level Security.

    There may be two possible options:

    1. Provide a slicer that allows users to select their time zone 
    2. Provide seamless conversion from UTC to the user's local time.

    I have not solved for either option and would very much welcome possible solutions for BOTH!

    Thanks very much!

    #Visualize #Model #Develop

    ​​​​

    ------------------------------
    Barry Powell
    Digital Insights Consultant
    Valorem Consulting
    Kansas City, MO
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Need Help! Converting UTC to local time (using Direct Query storage mode)

    Posted Jul 10, 2018 12:10 PM
    With lot of trail and errors, I couldn't get the automated way.

    For now, I am converting to +5 manually.

    Date Time (Local) = FORMAT( 'PBI Audit Log'[Creation Date (UTC)] - TIME(5,0,0), "General Date" )

    But I think, you can give a drop down slicer to choose the time zone and supply something like this TIME( SELECTEVALUE(TimeZone), 0, 0)

    ------------------------------
    Yaswanth (Yash) Muthakapalle
    Greater Chicago Area, IL
    (302) 724-9274
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Need Help! Converting UTC to local time (using Direct Query storage mode)

    Top Contributor
    Posted Jul 13, 2018 11:51 PM
    Edited by Audrey Abbey Jul 14, 2018 12:40 AM
    ETA: I just saw the Direct Query bit.
    DATEADD won't work.

    How much control do you have over your data source?
    Your hands are pretty tied on the Power BI side of the equation when it comes to dates, unfortunately.



    Hmmmm.

    You would need a table with the time zone list, and the UTC offset, stored as an integer (positive or negative), including daylight savings time options.
    Might need a concatenated column with Time Zone + (offset)
    Then DAX measures to capture the selected slicer value  and extract the offset value.

    After that, you could create a calculated column with DAX, something like this:
    LocalDateTime =DATEADD(YourTable[UTCDateTimeField],[Offset Measure],hour)

    You might need to use a calculate and variable, rather than a straight dateadd.

    This would create a new calculated date column that is adjusted on the fly for the offset that the user selected.
    Depending on how large the data set is, this may introduce performance issues.

    Unfortunately, this does require a list of time zones and offsets.
    Calculating "local" time vs utc is difficult once you publish to the cloud, as "local" is server time, not actual local time.

    I will see if I can get an example working. I have a dataset with UTC times handy.







    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    New Seasons Market
    Portland OR
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Need Help! Converting UTC to local time (using Direct Query storage mode)

    Posted Jul 14, 2018 10:36 AM
    Did you try changing the setting or Direct Query ?

    File --> Options and Settings --> Options --> DirectQuery -->
    Select "Allow unrestricted measures in DirectQuery mode"


    ------------------------------
    Yaswanth (Yash) Muthakapalle
    Greater Chicago Area, IL
    (302) 724-9274
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Need Help! Converting UTC to local time (using Direct Query storage mode)

    Posted Jan 25, 2019 05:49 AM
    Edited by Adam Marshall Jan 25, 2019 05:53 AM
    Hi Barry

    I had the same problem and think I've found a solution.

    I created a query with two columns and one row. One column for UTC (DateTimeZone.UtcNow) and one for LocalTime (DateTimeZone.FixedLocalNow). I then added a new column using Time.Hour to calculate the difference, and then converted this value to a list. Next, I created a new parameter which referenced this list, then I inserted this parameter into a new column in my direct query table. So, I my local time is 4 hours ahead of UTC, I'll have a new column in my direct query table with 4 on every row.

    Finally, I created a new calculated column in my direct query table to adjust the hour of my DateTime value by the adjustment.
    DATE(year(komfort_Samples_Last_Hour[sampledAt]), Month(komfort_Samples_Last_Hour[sampledAt]), Day(komfort_Samples_Last_Hour[sampledAt]))
    +
    TIME(hour(komfort_Samples_Last_Hour[sampledAt])+Komfort_Samples_Last_Hour[TimeAdjustment], minute(komfort_Samples_Last_Hour[sampledAt]),second(komfort_Samples_Last_Hour[sampledAt]))

    Note: this query will be imported, therefore there could be an instance when daylight savings has changed but your import hasn't yet run, depending on how often you schedule the refresh. But it should be fine 99.9% of the time.

    Adam
    Conference-PBI_200x200


  • 6.  RE: Need Help! Converting UTC to local time (using Direct Query storage mode)

    Bronze Contributor
    Posted Apr 11, 2019 11:03 AM
    Hi guys.

    Seeing another user ask about TimeZone conversion, I've posted my solution on github.  Hope it helps.
    https://github.com/ryanperrymba/PowerBIConvertUTCtoLocalTime

    ------------------------------
    Ryan Perry
    Business Systems Analyst
    Auric Solar
    ------------------------------

    Conference-PBI_200x200