Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
lkshck
Helper III
Helper III

Last Refresh Date change Timezone

Hey,

I added a new Table with one column to my report which should show the Last Refresh Date. I've created it with the following query:

let
Source = #table(type table[Date Last Refreshed=datetime], {{DateTime.LocalNow()}})
in
Source

In the PowerBI Desktop file during editing the correct time is shown but if I publish the Report to PowerBI Service and data gets refreshed, UTC time is shown. Is it possible to have a fixed timezone?

Here is a screenshot what the field shows in PowerBI Service after refresh.

Xnip2022-03-02_08-25-16.jpg

Normally it should show 3/2/2022 12:00:22 AM as it refreshs every day at 12am in the night. My current Timezone is CET.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@lkshck , I think you should use

Date.From(DateTime.FixedLocalNow())

 

for timezone refer if this can help

https://www.youtube.com/watch?v=2kmFfbOeFJg

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

17 REPLIES 17
jalber
New Member

I was able to use a SQL query to achieve this. For this solution, you'll need to be able to connect to a SQL server and database (any server/DB will do because you won't actually be querying a table).

 

From your Power BI Desktop file, click Get Data > SQL Server. Enter the Server name and Database name, then expand Advanced options. Paste the below code in the SQL statement field then click OK.

 

Note: This is for Pacific timezone, but you can modify this code for any timezone. To see the list of supported 

timezone names use SELECT * FROM sys.time_zone_info then replace the 'Pacific Standard Time' with the name of your timezone for both the second and third columns. Consider updating the column names since these reference PT for Pacific Time. Repeat the second and third lines of code with alternate timezones should you want more timezone options. Ultimately, only the third column matters, but I left the UTC and Offset columns to demonstrate how the query works.

 

SELECT 
 CAST(GETDATE() AT TIME ZONE 'UTC' AS DATETIME) AS [LastRefreshUTC]
,CAST(LEFT(RIGHT(GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time',6),3) AS INT) AS [PTOffset]
,DATEADD(HOUR,CAST(LEFT(RIGHT(GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time',6),3) AS INT),CAST(GETDATE() AT TIME ZONE 'UTC' AS DATETIME)) AS [LastRefreshPT]
ASHARYA1
Regular Visitor

Found the esiest solution create a column in date table or any table Updated_at=Now() and add this as the value in text box 

 

last updated at +value=max(Updated_at) 

 

Works like a charm 🙂

 

 

Ash2022
Frequent Visitor

This is how I used to get the local Date/Time using worldtimeapi.org:

 

1- Use https://worldtimeapi.org/api/timezone to find your local timezone path/page/API

2- The following Query will convert it into a readable date/time.. Update the WebPage path

let
   Source = Json.Document(Web.Contents("https://worldtimeapi.org/api/timezone/America/Toronto")),
   #"Converted to Table" = Record.ToTable(Source),
   #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value"),
   #"Removed Other Columns" = Table.SelectColumns(#"Pivoted Column",{"datetime"}),
   #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns","T"," ",Replacer.ReplaceText,{"datetime"}),
   #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [datetime],each Text.Split([datetime]," "){0} & " " & Text.Split(Text.Split([datetime]," "){1},"-"){0},Replacer.ReplaceValue,{"datetime"}),
   #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"datetime", type datetime}})
in
   #"Changed Type" 

 

mscla
Advocate I
Advocate I

I really recommend to get data online from https://www.timeanddate.com/worldclock/..

hectorveram
Frequent Visitor

Hi, good day,

I am an Amateur Bi User 🙂 

 

I have the Same issue, on the Bi App it shows correct date and time, but on Web Report show UTC time (in this case +4) is there any way to fix this?

i have used this Dax to get Dataset Refresh time but still with this issueDax usedDax usedDate & time on Bi App with correct timeDate & time on Bi App with correct timeDate & Time on Bi webDate & Time on Bi web

let
Source = #table(type table[Date Last Refreshed=datetime], {{DateTime.LocalNow()}}),
    #"Tipo cambiado" = Table.TransformColumnTypes(Source,{{"Date Last Refreshed", type datetime}})
in
#"Tipo cambiado"

 

Very appreciate the help

 

Thanks

Hey,

I used the following solution as a little hack as it was way more easier than the Dax solutions from my point of you.

I added a new Web data source to my file with the following URL:

http://worldtimeapi.org/api/timezone/Europe/Berlin

This will give you a new table with date information like utc-time and the time of Europe/Berlin like you can see here:

lkshck_0-1665384655749.png

I added the datetime value to a visual and that's it. Everytime the Report will be refreshed, this value will be also refreshed to the current time.

Maybe this will help you as well 🙂

 

Best regards,

Lukas

Of all the options the easiest and most effective is this, spend hours looking for a solution and here simply with new data source the updated time is shown in web version and desktop version in my case I needed to update it at the time of chile and modify the link http://worldtimeapi.org/api/timezone/america/santiago

Hello colleagues, good day.

I have a doubt that I have been trying to solve for days but I can not find that it is...

At the time of publishing the report, the information must be from today, however I get that it is already tomorrow.

For example: I occupy to know quantities of today (December 12) but when the report is published, it throws me the date of tomorrow (December 13).

They could guide me... Please.

Best regards

If you are using the previous method of the page you have to know which field you are selecting to show, it will not be that you are showing the UTC time and not that of your city.

Of all the fields that come out, which one you take for the board?

In the end I subtract the number of hours of difference from the UTC so that at the end of publishing the report I would keep the correct date and time. This causes that in the desktop report the time is not correct.

Syndicate_Admin
Administrator
Administrator

I ran into the same problem and at the moment I am trying to change the data type from date to text so that the power bi service does not detect it as a date and does not do the UTC convertion... I'll see how it goes.

amitchandak
Super User
Super User

@lkshck , I think you should use

Date.From(DateTime.FixedLocalNow())

 

for timezone refer if this can help

https://www.youtube.com/watch?v=2kmFfbOeFJg

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Date.From(DateTime.FixedLocalNow()) didn't worked. So if there isn't any build-in solution I will go with the external solution.

Hi @lkshck,

AFAIK, the current power bi service does not support DateTime values with the specific time zone. The DateTime with timezone will be force converted to UTC format if you used them on the power bi service side. 

For this scenario, I'd like to suggest you get the 'UTC NOW' on the desktop side and manually calculate it with the timezone offset to keep it shown in the specific timezone.

let
    //#duration(days, hours, minutes, seconds) function and negative/positive number to fix UTC to specific local timezone
    Source = DateTimeZone.UtcNow() + #duration(0,8,0,0)
in
    Source

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Great, it worked excellent!!

Will the first solution uses my local time also in PowerBI Service? If yes, this should be fine. I want to avoid adding new data sources if it's not really needed 😄 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.