Paginated reports date and time returns GMT timezone on PBI service

[Anonymous]

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)

[GB-Work]

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]

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]

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]

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

[bellrock-james]

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

[AndyAus]

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

@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