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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

6 REPLIES 6
GB-Work
New Member

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

 

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors