Hi,
Having trouble with the extended date table where the days offset won’t tick over when refreshing via the PBI service. Works just fine when refreshing via PBI desktop app.
I’ve figured out that the timezone on the PBI service is -10hours from me, so if I schedule a refresh for 6am my time, it’s 10pm the day before on the service. This means the day offset is still set to the previous day. If I schedule a refresh for 10am my time, it’s 12am for the service, and the refresh works correctly.
I need to refresh at 6am my time, how do I get around the problem ?
I attach a basic PBIX including a dummy spreadsheet datasource as for some reason you can’t schedule a refresh of a PBIX where there is only the date table and nothing else.
dumytable.xlsx (8.6 KB)
test date offets.pbix (179.3 KB)
Hello @BendeVries, and thank you for posting your question to the Community Forum.
Aaron Watsky in his LinkedIn Post Power BI Service Time Differences and How to Fix It offered the following guidance:
" Every Power BI Server (Where the Power BI Service lives) follows UTC (Coordinated Universal Time) for the server time. This is the universal time standard. You may have noticed that when you first publish your report the time stamp listed is correct it is only when you refresh that you see a difference. The reason for this is that Power BI refreshes time based on the localized time of the machine in which the report is refreshed on. So, refreshing on your local machine results in local time whereas refreshing on the services results in UTC.
So how can we solve this? There are a few ways, and one uses the Power BI Rest API as described in the MS Learn Guidance Datasets - Update Refresh Schedule and offers the following sample: Update the times of a refresh schedule example which patches the Dataset by using offsets in a variety of ways.
I have not personally tested this to see if the API is really limited to Datasets stored of MyWorkspace as the documentation suggests. I would want to believe otherwise.
–
If this post helps, then please consider Accepting it as the Solution to help the other members find it more quickly.
Did it contribute positively towards finding the final Solution? Please give it a like.
1 Like
Thanks @ystroman. Some good reading and might have found a solution.
I had an idea to swap out the CurrentDate function like this
existing code - this is running based on the local machine if refreshed locally, or UTC if on the service.
//CurrentDate = Date.From( DateTime.FixedLocalNow()),
trying this tonight - the extraction of the date itself looks a bit messy because the datetime field in the api is unusually long. ChatGPT suggested trying DateTimeZone. It’s working, hopefully will work on the power BI service tomorrow morning.
new code
TimeApiSource = Json.Document(Web.Contents(“http://worldtimeapi.org/api/timezone/Australia/Sydney”)),
CurrentDate = Date.From(DateTimeZone.FromText(TimeApiSource[datetime])),
Solved! Replace the currentdate function to below and this will update the calendar when scheduled on the PBI service. Just remember to change the api timezone string, and also you’ll need to authenticate the datasource credential for the web (dataset settings in service).
TimeApiSource = Json.Document(Web.Contents(“http://worldtimeapi.org/api/timezone/Australia/Sydney”)),
CurrentDate1 = TimeApiSource[datetime],
positionOfT = Text.PositionOf(CurrentDate1, “T”),
dateText = Text.Start(CurrentDate1,positionOfT),
CurrentDate = Date.FromText(dateText),