GMT to UTC conversion to display reports's run time in UTC time zone

[Anonymous]

Hi all,

How should I display a Power BI Report Builder report's run time in UTC time zone? Power BI service has it in the GMT zone and our customers want it in UTC. I am using [&ExecutionTime] in report's footing to display report's run time. I have searched different posts and suggestions, but have not been able to make them work in the report's footing.

Thank you!

Seyed

[Syndicate_Admin]

This solution works for Paginated Reports (or SSRS) the concept can also be applied to Power BI
==================================================================
CORRECT LOCAL TIME USING POWER BI SERVICE - INSTEAD OF UTC TIME
==================================================================
For Reported Paginados : I had to implement this fix...
If you're using on-premises SQL Server; creates a new DataSet
Mine is : LOCAL_Datetime
use this 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 running, you will see a ROW with 3 FIELDS that you can use directly in your reports:
MILTIME = Military Time [ 17:21 ]
REP_TIME = LOCAL TIME [ 5:21PM ]
REP_DATE = LOCAL DATE [ 12/11/2020 5:21PM ]

You'd have to change
= Today() or =Now()
by

=First(Fields! REP_DATE. Value, "LOCAL_Datetime")

For hours uses:
=First(Fields! REP_TIME. Value, "LOCAL_Datetime")

You can also drag the REP_DATE field to the report (header, body, or footer)
you can change the date format (if you change VARCHAR to datetime)

For ORACLE you'll need to change the Query... using sysdate() and/or appropriate date handling functions.
It works in headers and footers too... Luck! - You must take into account the change in daylight saving time.
======================================================================
Andres Martinez
Sr Data Analytics Engineer - Targa Resources

[Anonymous · Accepted solution]

This issue got resolved after I opened a service request with Microsoft Support. The engineer assigned to the service request, shared two links, one of which listed below helped with achiving the right result:

https://community.fabric.microsoft.com/t5/Service/Paginated-reports-date-and-time-returns-GMT-timezo...

Solution consisted of two steps:

Step 1 - Create a VB function that pulls in time in the local time zone, for us it would be 'Central Standard Time'

1. In Microsoft Power BI Report Builder, click on 'Report Properties' and then 'Code'.
2. In the 'Code' section, entered the following VB code to create a function calle GetCST 

   Public Function GetCST As DateTime

   return TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time"))

   End Function

Step 2 - Use the new VB function in the footing

1. In report's footing, replaced '[&ExecutionTime]' with '=CodeGetCST()'

Finally, saved and published the paginated report in the selected workspace and ran the report, the run time was in CST time zone.

Seyed

[LouisvZ]

Hi!

Thanks for sharing this. It is a good solution and works well. Just one minor correctio. The code for executing that custom function is this:

'=Code.CodeGetCST()'

[Idrissshatila]

Hello @Anonymous ,

I solved this issue using this video, check it out https://youtu.be/eH9dHfNG8kg

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[Anonymous]

Hi Idrissshatila,

Thank you for assisting me with this issue. I tried the solution offered in that YouTube video you recommended. I believe that could be used to get last data refresh time. For us, data conains historical information and there is no need to refresh data for running this report every time. We just need the ability to display report's run time in the footing of the report. There has to be a better way to pull the  report's run time in CST time zone.

Thanks again!

Seyed