Last Refresh Date change Timezone

[lkshck]

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.

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.

[jalber]

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]

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]

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]

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

[hectorveram]

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 usedDate & time on Bi App with correct timeDate & 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

[lkshck]

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:

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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.

[Syndicate_Admin]

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

[Syndicate_Admin]

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]

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 · Accepted solution]

@lkshck , I think you should use

Date.From(DateTime.FixedLocalNow())

for timezone refer if this can help

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

[lkshck]

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

[v-shex-msft]

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

[Syndicate_Admin]

Great, it worked excellent!!

[lkshck]

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 😄