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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Paginated reports date and time returns GMT timezone on PBI service

Paginated reports using Globals!ExecutionTime (Built-in Field) or =Now() and =Today() expressions on the Power BI service show the GMT date and time. The date and time for these functions should be the local date and time from the the region of tenant. 

 

E.g If your data is stored in Australia Southeast (Victoria) the date and time shows as GMT+1000 (Australian Eastern Standard Time)

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

Actually, functions like now() will return UTC time on service, please refer to case and workaround below:
https://community.powerbi.com/t5/Service/Set-local-time-zone-Power-BI-Service/td-p/114120

 

Regards,

Jimmy Tao

View solution in original post

7 REPLIES 7
GB-Work
Regular Visitor

Reposting a simple solution from a comment on a previously linked blog (see blog comment at https://pivotaldata.blogspot.com/2019/10/timezone-trouble-current-date-and-time.html?showComment=166...)

 

Returns local time, when run in desktop app but UTC time when run in service

=Globals!ExecutionTime

Returns local time when run in desktop app and service

=TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("US Eastern Standard Time"))

^just make sure to replace the timezone with the one you need!

andres777
Advocate III
Advocate III

If one of your sources is a database, just use the datetime from your local database,
which should be in your own timezone,
and Summer Time should be taken care of...

==================================================================
CORRECT LOCAL HOUR ON POWER BI SERVICE - INSTEAD OF UTC TIME
==================================================================
For Paginated Reports : I had to do this custom Fix...
Workaround: if you are connected to any local SQL Server ; create a new DataSet
I called mine : LOCAL_Datetime
with the following QUERY :
-------------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(5), GETDATE(), 108) MILTIME ,
RIGHT( LTRIM(RIGHT(CONVERT(varchar, GETDATE(), 100),8)),8) REP_TIME ,
CONVERT(VARCHAR(10),GETDATE(), 101) + RIGHT(CONVERT(VARCHAR(32),GETDATE(),100),8) REP_DATE
-------------------------------------------------------------------------------------------
When you run it, It will produce 1 ROW with 3 FIELDS you can use in your Power BI Report Expressions :
MILTIME = Military Time [ 17:21 ]
REP_TIME = LOCAL TIME [ 5:21PM ]
REP_DATE = LOCAL DATE [ 11/12/2020 5:21PM ]

 

In the Expression you need to change
= Today() or Now()
by

=First(Fields!REP_DATE.Value, "LOCAL_Datetime")
and format as a Date format "1/31/2000"

For Time use:
=First(Fields!REP_TIME.Value, "LOCAL_Datetime")

You can also drag and drop the REP_DATE field
from the left Dataset column into the Report and it will populate the formula for you.
you can then format the same value (if you change the query to use datetime format instead of varchar)


For ORACLE you will need to update the Query... using sysdate()
It works with headers and footers too...Enjoy!
======================================================================
Andres Martinez
Sr Data Analytics Engineer

garythomannCoGC
Impactful Individual
Impactful Individual

Cheers  @AndyAus   I hope so :}   No one has complained yet with the daylight saving change over.

And just checked but no.  Both on the service and running locally my time comes up 1 hour ahead yet I am Queensland based.  Probably time is sourced off the server located in NSW.

garythomannCoGC
Impactful Individual
Impactful Individual

Timezone Trouble - Current Date and Time for Paginated Reports on Power BI 

I like this method.  No need for hidden parameter though just plug the call into a textbox label.

And for posterity just in case lets summate here.

Create the function below, Report Properties > Code > Custom code window.

Do not alter or format the code, copy verbatim.

Setup the function call, for example

Textbox > Selected Text > General.Value  set to  =Code.GetAEDT

 

Public Function GetAEDT As DateTime
return TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("AUS Eastern Standard Time"))
End Function

 

I prefer this method. Thank you for including the summation. 

Thank you Garry this is Gold!! I assume, as its converting to AEDT that daylight savings is automatically allowed for as well (happy to be corrected on that)

v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

Actually, functions like now() will return UTC time on service, please refer to case and workaround below:
https://community.powerbi.com/t5/Service/Set-local-time-zone-Power-BI-Service/td-p/114120

 

Regards,

Jimmy Tao

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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