Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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)
Solved! Go to 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
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!
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
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.
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)
@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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
38 | |
27 | |
17 | |
17 | |
8 |
User | Count |
---|---|
45 | |
38 | |
34 | |
17 | |
16 |