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
= '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
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 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 )
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.
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
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.