Forum Discussion

laurahoff97's avatar
laurahoff97
Frequent Visitor
1 year ago
Solved

Local Time Functions and Publishing to Server

I know this is a common issue dealing with the conversion of times to UTC when publishing to server but I am hoping someone can help me with functions that pull current date and time.

I have a refresh date on my dashboard using the NOW() funtion to pull the time my dashboard is refreshed in to show users the last time the data was updated. 

However when publishing to server of course even with the scheduled refreshes the time is adjusted for UTC.

 

Which is a time that hasn't even happened in my time zone yet. What formulas can I use to account for the UTC conversion but also still confirm I am pulling the exact time the dashboard was updated?

 

 

Need Help
  • v-sshirivolu's avatar
    v-sshirivolu
    1 year ago

    Hi laurahoff97 ,
    Thanks for reaching out to the Microsoft fabric community forum.

    I encountered the common challenge where NOW() returns UTC time in Power BI Service, while it provides local time in Desktop. To ensure precise local time (such as IST or PST) is displayed on a report without needing a gateway, I implemented a reliable solution using a public time API

     

    Steps to Achieve This -

    Create a Blank Query

    Navigate to: Home - Transform Data - Transform Data - Home - New Source - Blank Query

    Use Advanced Editor

    Select the query (e.g., Query1)

    Go to Advanced Editor

     

    Insert this M code (for Asia/Kolkata timezone):

    let
        Source = Json.Document(Web.Contents("https://timeapi.io/api/Time/current/zone?timeZone=Asia/Kolkata")),
        datetime = Source[dateTime],
        DateTimeConverted = DateTimeZone.FromText(datetime)
    in
        DateTimeConverted

     

    Configure Authentication

    When prompted, select Anonymous and click Connect.

     

    Apply Your Changes

    Rename the query to CurrentLocalTime.

    Click Close & Apply.

     

    Add to Report

    Insert a Card visual

    Drag the CurrentLocalTime field onto the card

    Customize the Card

    In the Format pane: Disable Category label , set Display units to None, adjust text size as preferred

     

    This method retrieves the actual local time via HTTPS with no gateway required. Functions seamlessly in both Desktop and Power BI Service. Eliminates the NOW() UTC offset issue. Automatically adjusts for DST and works globally.


    Please find the attached .pbix file for your reference.

    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

    Best Regards,
    Sreeteja.
    Community Support Team 







6 Replies

  • ajaybabuinturi's avatar
    ajaybabuinturi
    Super User
    1 year ago

    Hi laurahoff97,

    You can try with below M-Code. You can just replace your time zone in the below code.

    let
    Source = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), +5,30),
    #"Converted to Table" = #table(1, {{Source}})
in
    #"Converted to Table"

     

    Syntax:

    DateTimeZone.SwitchZone(dateTimeZone as nullable datetimezone, timezoneHours(+5) as number, optional timezoneMinutes as nullable number) 

     

    dateTimeZone: DateTimeZone.UtcNow()

    timezoneHours: Set your time zone in hours

    timezoneMinutes: Set your time zone in minutes 

     

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

     

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    1 year ago

    Hi laurahoff97 ,
    Thanks for reaching out to the Microsoft fabric community forum.

    I encountered the common challenge where NOW() returns UTC time in Power BI Service, while it provides local time in Desktop. To ensure precise local time (such as IST or PST) is displayed on a report without needing a gateway, I implemented a reliable solution using a public time API

     

    Steps to Achieve This -

    Create a Blank Query

    Navigate to: Home - Transform Data - Transform Data - Home - New Source - Blank Query

    Use Advanced Editor

    Select the query (e.g., Query1)

    Go to Advanced Editor

     

    Insert this M code (for Asia/Kolkata timezone):

    let
        Source = Json.Document(Web.Contents("https://timeapi.io/api/Time/current/zone?timeZone=Asia/Kolkata")),
        datetime = Source[dateTime],
        DateTimeConverted = DateTimeZone.FromText(datetime)
    in
        DateTimeConverted

     

    Configure Authentication

    When prompted, select Anonymous and click Connect.

     

    Apply Your Changes

    Rename the query to CurrentLocalTime.

    Click Close & Apply.

     

    Add to Report

    Insert a Card visual

    Drag the CurrentLocalTime field onto the card

    Customize the Card

    In the Format pane: Disable Category label , set Display units to None, adjust text size as preferred

     

    This method retrieves the actual local time via HTTPS with no gateway required. Functions seamlessly in both Desktop and Power BI Service. Eliminates the NOW() UTC offset issue. Automatically adjusts for DST and works globally.


    Please find the attached .pbix file for your reference.

    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

    Best Regards,
    Sreeteja.
    Community Support Team 







    • v-sshirivolu's avatar
      v-sshirivolu
      Community Support
      1 year ago

      Hi laurahoff97 ,

      I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. 

      • v-sshirivolu's avatar
        v-sshirivolu
        Community Support
        1 year ago
        Hi laurahoff97 ,

        I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us.