Many of us are working on projects moving from local servers to more cloud based services, like Power BI. The Power BI service can be hosted anywhere and even though the data might be reside in a certain location, Date & Time functions for your reports may behave differently than you expect in the Power BI service.
Recently I found this out using functions like NOW(), TODAY() and global variables like Execution Time in Power BI Paginated Reports to get the current date and time. Paginated Reports are new to Power BI and based on SQL Server Reporting Services (SSRS). These functions and variables were around in SSRS, but would be deployed to a specific Reporting Services servers.
Now, we can develop Paginated Reports in the Power BI Report Builder application and deploy these reports to the Power BI service. If you use these functions to get current date and time, what you see in Report Builder, may not be what you see after you deploy the the Power BI service.
Let me show you what I mean...
Date & Time Functions
Below (on left) is a report in Report Builder that shows the date/time output of the functions NOW() and TODAY(), as well as the global variable Execution Time. This report is run on my PC in the timezone AEDT (UTC+11).
Minutes later I deploy this report to the Power BI service. Above (on right) is the output on the Power BI. As you can see the same report, on the Power BI Service, gives time result that are 11 hours earlier for the Now() function and global variable. And a day difference for the Today() function.
On the Power BI service i'm getting current date/time results for the GMT (UTC+0) timezone. These functions and variables are intended to return this UTC time on the Power BI Service.
AEDT has daylight savings time. So half of the year it will be UTC+11 the other UTC+10, so adding 11 hours to the expression won't work, plus when developing the report I will see time 11 hours ahead.
So what do I do?
Custom Code Solution
In the report properties we can add a small custom code function. This function will return the current time for a desired timezone, by getting UTC time and converting it to the time for this timezone. We can then call this function from a report parameter and use it with in the report.
1. Open the Custom Code Property for the report. Right click in the grey area of the report designer pane, select Report Properties.. and go to the Code tab.
2. Write a small custom function. Copy the following code to the custom code area.
Public Function GetAEDT As DateTime
return TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("AUS Eastern Standard Time"))
End Function
This custom function (named GetAEDT) will get the the UTC time and convert it to AEDT time.
3. Adjust code accordingly. Adjust the function name and TimeZoneById value to the timezone you need.
4. Config a parameter to call the code. Create a report parameter, go to it's properties. Then the Default Values tab, select Specify values and Add a value. Select the expression button to the right of the value and write in Set expression for Value
=Code.[your function name]()
See example below
5. Use the Parameter in your report.
And that is it. Run your report and deploy it. You will get the same time in Power BI Report Builder and on the Power BI service for your paginated report. In my case it was the AEDT time.
I'd love to hear if this helped you out or if there are other ways you handled current date and time on the Power BI Service or in Paginated reports. Please shared in the comment below. I look forward to hearing from you.
Can I convert 2 time zone in the report? I want to convert UTC to EST and MST.
ReplyDeleteYes, write 2 functions in the reports custom code section (one for EST and one for MST). Refer here for the correct Time Zone values: https://stackoverflow.com/questions/58655523/timezone-index-values. Once your functions are ready, create two parameters to call each function and use the parameters in your report.
DeleteHi Steven,
ReplyDeleteI am trying to use the above to filter a count of records to those with a admission date of yesterday so when the published report is scheduled, it will filter correctly.
I attempted to use for the filter expression: =DateDiff("h",Format(CDate(Fields!AdmissionDate_Utc.Value)), Today) with the Operator Between -14 and 10. This most worked except sometimes it's picking up a record from the day before.
How can I use the new parameter to do this?
I believe the Today function is where the issue is. The function description is 'Returns or sets a Date value containing the current date according to your system'. As it is a system date value you cant be assured this is in the utc timezone.
DeleteTry following the steps above to creating a custom getUTC function (simplified to just return DateTime.UtcNow) with report parameter (e.g. ReportParameter1) Then try the following expression:
=DateDiff("h",Format(CDate(Fields!AdmissionDate_Utc.Value)),Parameters!ReportParameter1.Value)
This should assure you are comparing two utc datetimes.
Thank you for this post! I've been looking for a solution for this issue for a couple of days and thankfully found your post in another post's comment!
ReplyDeleteI have a couple of related questions:
1- I'm assuming this code will take into account the changes in daylight savings?
2- Any idea how to change the format of the date and time for that expression?
Thank you
Hi,
DeleteThanks for your questions.
1 - Yes, it will
2 - One way to change the format would be to apply a date function around the AEST function call when setting the report parameter. For example instead of =Code.GetAEST() write something like =FormatDateTime(Code.GetAEST(), DateFormat.ShortDate). You will find more useful date functions under the Category Pane -> Common Functions -> Date & Time.
I hope this helps :)
I didn't need to create a function. I simply entered the code "TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("US Eastern Standard Time"))" in the text box expression. I just changed the time zone to US Eastern Standard Time as it was the location I needed the time converted. Thanks!
ReplyDelete