Forum Discussion
Time zone issue with daily sales clock
I'm going on circles trying to get this to work.
I need to report sales for today all the way to the end of the day. For some reason, the folks who built this platform think that everyone is on UTC time. I need to compensate for the time differnce and not have the data go blank at 4pm since the UTC time shows its the next day.
I have tried everything I can think of and have searched high and low for answers in blogs and forums.
Even though my data shows the time stamp as today, after 4pm, PowerBI thinks its the next day and wont recognize today in my time zone.
There needs to be a setting in Power BI that allows me to choose the relative time date stamp so that I can have valid results.
If anyone knows a way around this problem, please let me know
Yes, I figured it out finally, no help from MS of course. I had to create a few new columns, you'll have to adjust for your time zone.
Start in desktop, and then you have to publilsh and see where you are at. I used the order details to match it all back up with the realaity of what I knew was right, then adjust time zone as needed in the dektop and re-publish to outsmart the program.
'Now = NOW() - 7/24'
'Reporting Date = Appraisal[CreatedDate] -7/24'
'Corrected time and date Stamp = DATEDIFF(Appraisal[Now], Appraisal[Reporting Date], DAY)'
Then use an advanced filter to only report 0 diff data based on the corrected time and date column.
5 Replies
- MarcelBeug
Community Champion
The folks who built this plarform realize that there are many time zones around the globe and they provided functionality in the Query Editor to switch date/times between UTC and a time zone with a fixed UTC offset.
The folk who is asking the question should provide more detailed information:
- how exactly does your date/times look like in the Query Editor (probably it are actually date/time/zone values with +0:00)
- what is your local time zone? Is daylight saving time observed and if so, what is the pattern of the clock switches?
Just to illustrate, this is how my local time looks like in the Query Editor, along with the local UTC time (I'm on UTC + 1:00 currently).
This will not solve the issue. My time stamp is correct and is in Pacific time. The issue is that the PowerBI server is set to refresh on the UTC time stamp and in the afternoon the time is 16:00 here in CA, an the UTC date stamp rolls over to 00:01 tghe next day, thus reporting sales for that day in my report for the next day which has not come to be in CA yet.
For example since you wanted to see it, hereis my query, this shows the created date of 1/9/18 at 8:25PM LOCAL time. This shows up on the next day report since the UTC time is 1/10/18 at 04:25 AM
- MarcelBeug
So it's all about the scheduled refresh (and your report definitions).
I have no experience with scheduled refresh, but according to this information from Microsoft, it can be configured for a specific time zone.
