cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CRamirez Regular Visitor
Regular Visitor

Direct Query UTC workaround

Hello,

 

I have what seems to be a unique issue as I haven't found an adequate solution anywhere on here after a couple hours of searching. This has to do with the UTC conversion that occurs on PowerBI service, regardless of time zone setings on PowerBI Desktop. I am aware of the DateTime.AddZone custom column solution; but that only works for imported data which is not an option for the report I'm working on.

 

My Date dimension table has dates stored as  (m/dd/yyyy h:mm:ss tt) but the time is always 12:00:00 AM. Some solutions I have read have suggested subracting from my date column in the form of:

=
'dim_dates'[date1] - HOUR ( 'dim_dates'[date1] )
    - 5 / 24

 or:

=
'dim_dates'[date1] - TIME ( 5, 0, 0 )

but these solutions do not suffice.

 

 

The reason neither of those solutions work is because I have many measures in this dataset relying on time intelligence functions. In addition, all of the visuals on this report are using various relative time filters (this day, month,year). The above functions causes an offset of an entire day which results in incorrect displayed data.

 

Is there a way to create a function in DirectQuery that does something similar to  DateTime.AddZone, but doesn't roll me back to the previous day?

Ultimately I need this report to send an accurate dashboard subscription once a day after this dataset is refreshed 9:00pm Central U.S. time. Currently, a subscription that gets sent out at 9:00pm Central time displays information for the next day. For example, a subscription sent out September 12, 2017 at 9:00pm, would show September 13, 2017 which results in blank visuals because it isn't that day yet.

 

Any help would be appreciated 

5 REPLIES 5
Super User
Super User

Re: Direct Query UTC workaround

Hi @CRamirez

 

What I would suggest if your source is a SQL Server, why not change the source Date and Time to go back related to your specific timezone.


So if you are in GMT-6 then I would do a DateAdd(Hour,-6,MyDateTimeColumn)

 

So when that loads into Power BI Locally it will look incorrect, but when it is refreshed in the Power BI Service it should then reflect the right values?

Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Community Support Team
Community Support Team

Re: Direct Query UTC workaround

Hi @CRamirez,

 

Did you use a function that will return the real-time time? Such as Now and Today. If not, I think you don't need to convert time. Because all the time are in the same time zone. 

 

If you used them, there are two time zones now. It would be easier to change the result of Now. 

 

As you can see from the picture, The two parts in the blue rectangle are the same. Because they are static. The result of Now in the Desktop is different from it in the Service. But the result of NowForService and Now are the same, because I add a time zone. 

NowForService =
NOW () + TIME ( 8, 0, 0 )

 

Direct Query UTC workaround.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

A refrence: https://community.powerbi.com/t5/Service/changing-timezone-of-powerbi-service/m-p/153663/highlight/t... 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CRamirez Regular Visitor
Regular Visitor

Re: Direct Query UTC workaround

Hi @GilbertQ,

 

Thank you for taking your time to respond. I did try to shift the time back against my date/time column.

The problem that ended up happening was that my relative time filters were then thrown off giving me some August data for September visuals. This is due to my date/time column always showing 12:00:00am, so any changes backwards sets me a day behind. This also led to a lot of measured monthly/daily goals being off slightly.

 

I really wish we could set a specific timezone within the service so it wasn't such a headache to make sure our desktop/service data reconciled.

CRamirez Regular Visitor
Regular Visitor

Re: Direct Query UTC workaround

Hello @v-jiascu-msft,

 

That's a great function to be aware of thank you for bringing that to my attention! Unfortunately the problem I'm having is that, like the data in the blue rectangles, my date table is static. On the service side, once it becomes 12:00:00am UTC time, my date table rolls over to the next date regardless. The NowForService function will be great for daily data, but in this specific report I also highlight mtd & ytd so NowForService ends up not helping as much.

 

Trying to roll my date/time table back 5 hours ends up giving me the previous day because our date/time column is always 12:00:00am. This results in a variety of problems with relative date filters and the time-based measures I have in place. Is there a way I can just set my service side application to my timezone? It would relieve so much headache regarding these issues.

 

Thank you again for taking your time to help

Highlighted
Super User
Super User

Re: Direct Query UTC workaround

Hi @CRamirez

 

What if you changed your column data type from DateTime to Date. This would then eliminate the hours and when you upload it to the Power BI Service, it should then keep the same dates?

 

I think because it currently is a date time and the time is set to 12:00:00 AM it is then going back 5 hours making it 19:00:00 which then is taking it back that one day?

 

I am fairly certain in the past when I was only using Date fields I did not run into this issue.

Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"