Power BI Exchange

Expand all | Collapse all

Converting UTC to Local Time

  • 1.  Converting UTC to Local Time

    Gold Contributor
    Posted 11 days ago
    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
    ------------------------------


  • 2.  RE: Converting UTC to Local Time

    Top Contributor
    Posted 11 days ago
    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
    ------------------------------



  • 3.  RE: Converting UTC to Local Time

    Bronze Contributor
    Posted 10 days ago
    Edited by Ryan Perry 10 days ago
    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
    ------------------------------



  • 4.  RE: Converting UTC to Local Time

    Gold Contributor
    Posted 6 days ago
    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
    ------------------------------



  • 5.  RE: Converting UTC to Local Time

    Silver Contributor
    Posted 5 days ago
    "last refresh" is meaningless unless you have something in your data that can indicate the last time the data was added/modified.

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



  • 6.  RE: Converting UTC to Local Time

    Posted 5 days ago
    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
    ------------------------------



  • 7.  RE: Converting UTC to Local Time

    Bronze Contributor
    Posted 5 days ago
    @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
    ------------------------------