Local Time Functions and Publishing to Server

[laurahoff97]

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?

[v-sshirivolu · Accepted solution]

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]

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]

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]

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. We are here to support you and will be happy to help with any further assistance you may need.

[ajaybabuinturi]

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.

[speedramps]

See this video  
https://www.youtube.com/watch?v=CEJEtZu_xcg

Pleaase click thumbs up and [accept solution]