Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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 Pow...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

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 )

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

Best Regards!

Dale

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

  • GilbertQ's avatar
    GilbertQ
    Super User
    8 years ago

    Hi Anonymous

     

    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.

  • rahulvodoor's avatar
    rahulvodoor
    New Member
    6 years ago

    Hi, 

    I am facing a similar sitution. Were you able to get a workaround? What was your approach?