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

Converting UTC to Local Time

  • 1.  Converting UTC to Local Time

    Gold Contributor
    Posted Apr 10, 2019 03:56 PM
    Hi.  I added this code to give me a "last refresh" date.  This works great on the desktop version but every time I publish it goes to Power BI web service which uses UTC.  How can I keep it in my local time?

    Refresh date code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.FromRows({{DateTime.LocalNow()}},{"Last Refresh"})
    in
    #"Changed Type"



    ------------------------------
    David Yee
    Foothill Packing
    Salinas CA
    ------------------------------
    Power Summit (bi) - Post


  • 2.  RE: Converting UTC to Local Time

    Top Contributor
    Posted Apr 11, 2019 04:38 AM
    Hi,

    Use DAX to convert it on client side;

    Create a Measure & do conversion calculation there & place this into card visual.

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Sr. BI Consultant
    Karachi, Pakistan
    ------------------------------

    Power Summit (bi) - Post


  • 3.  RE: Converting UTC to Local Time

    Bronze Contributor
    Posted Apr 11, 2019 10:50 AM
    Edited by Ryan Perry Apr 11, 2019 10:52 AM
    Hi David,

    This is a well known limitation due to the Query engine using the Timezone of the PBI Server. There is no setting to change it.  I've worked with MSFT to come up with a solution, and reportedly they are eventually going to develop a full fledged solution to address this.  In the mean time,  Upon reading your thread, I decided to post my solution on github for the community to view.

    https://github.com/ryanperrymba/PowerBIConvertUTCtoLocalTime/blob/master/README.md

    We are using in production environment, but be aware of the limitations.  This is offered for educational purposes with no guarantees whatsoever.

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

    Power Summit (bi) - Post


  • 4.  RE: Converting UTC to Local Time

    Gold Contributor
    Posted Apr 15, 2019 05:06 PM
    Hey David

    Once you publish it to PowerbI service, use Microsoft flow to convert your dates to local time through API access, it is free, easily done and trending as well.

    sam

    ------------------------------
    Sam Chatterjee
    Business Intelligence Developer
    Sam Analytiks
    (M):-(+45) 9163 4228
    URL:- https://www.samanalytiks.com
    ------------------------------

    Power Summit (bi) - Post


  • 5.  RE: Converting UTC to Local Time

    Silver Contributor
    Posted Apr 16, 2019 06:14 AM
    "last refresh" is meaningless unless you have something in your data that can indicate the last time the data was added/modified.

    ------------------------------
    Lutz
    ------------------------------

    Power Summit (bi) - Post


  • 6.  RE: Converting UTC to Local Time

    Posted Apr 16, 2019 08:32 AM
    Hello Sam,

    Can you elaborate a little further on how this would be accomplished?  I was just asked this question yesterday and was unable to provide an answer.

    ------------------------------
    Kevin Johnston
    North Richland Hills TX
    8178748506
    ------------------------------

    Power Summit (bi) - Post


  • 7.  RE: Converting UTC to Local Time

    Bronze Contributor
    Posted Apr 16, 2019 10:24 AM
    @Sam Chatterjee, Second that.  What's this about a way to convert UTC dates in a PBI report to a specified Local time via Flow?

    ------------------------------
    Ryan Perry
    Business Systems Analyst
    Auric Solar
    West Valley City UT
    (801) 878-3363
    ------------------------------

    Power Summit (bi) - Post