Issues with Time Calculations(M/Power Query & Dax) in PowerBI Service (EST & UTC)

[jaykiu]

Hello,

I'm having issues with time calculations due to the difference between server time and local time.

I utilize time calculations in 3 different places:

M/Power Query: I have three production shifts that I track, 12:15AM - 7:29AM(Midnight), 7:30 AM-4:14 PM (AM), 4:15 PM-12:14AM (PM)

1. I create a production date for the purpose of keeping the data from 12:00am-12:14am (EST) on the previous day.
2. I tag the data row with the associated shift.

From DAX:
1. I calculate the time delta between shift start time and first entry of the shift.

2. I calculate the time delta between the last entry of the shift and shift end.

Everything works great on desktop version, but I can't figure out how to have it work on the service. On upload everything works as expected but on server side refresh it changes to UTC time and my numbers are all off.

I've tried setting all time fields to UTC time to try to stop the conversion from happening, I don't care about having accurate time during the day. I care that my logic remains constant and that the data is correct at the week level.

Does anyone have any hints on how to fix this?

[v-yueyunzh-msft]

Hi, @jaykiu 

Power BI is a cloud service, and that means Power BI files are hosted somewhere. Some DAX functions such as Date/Time functions work on system date/time on the server their file is hosted on. So If you use DAX functions such as TODAY() or NOW() you will not get your local date/time, You will fetch server’s date/time.

For a solution to this problem, you can refer to the following documentation:

solving-time-zone-issue-in-power-bi 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[edhans]

The service is always in UTC. You need to use the DateTimeZone functions in Power Query so the data will work in both the desktop and service. The DateTimeZone.SwitchTimeZone function is particularly useful.

DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8))

That will return the current time in the Pacific timezone in the service (which is -8 offset to UTC). Power BI Desktop will also recognize this, so it will be in sync.

You can see this in use in this article. That might help put it in context. Add a Refresh Time Stamp To Your Power BI Reports — ehansalytics

[jaykiu]

I see that it takes the datetimelocal.now() if I was using this to transform a column / create custom column. Would it be 

DateTime.Date(DateTimeZone.SwitchZone([DateTimeColumn],-8))

 ?

[edhans]

It depends on the value in that column. If there is no timezone, you'd need to build it by splitting it out, then reassembling it in the #datetimezone - PowerQuery M | Microsoft Learn function.

[edhans]

You will need to play with it. The DateTimeZone.From can often convert to a timezone, then allow you to switch timezones.