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.
