I cannot figure out why this is happening. The actual time stamps match up but when I publish the report and view it immediately on the cloud, it flips the data from 'today' to 'yesterday'. I work p/t evenings and my manager says it reports correctly in the mornings and then flips over in the afternoon. Is it something to do with time zones? I was not able to see anything on cloud settings to change the time zone. Any ideas?
My formulas are the following:
CreatedDateIsToday = Created[CreatedDateOnly]=today()
Created Today = "Total Created Today:"&" "&CALCULATE(Created[Assignments Created],Created[CreatedDateIsToday]=TRUE)
CreatedDateIsYesterday = Created[CreatedDateOnly]=today()-1
Created Yesterday = "Total Created Yesterday: "&CALCULATE([Assignments Created],Created[CreatedDateIsYesterday]=TRUE)
There is a known gap for the time zone displayed in Power BI Service. The time zone in the service is effectively always set to UTC.
For example, if we use NOW() as a measure in Power BI Desktop, it can give the correct time based on your current time zone. However, once you publish to Power BI Service, the time will be adjust to UTC time.
There is a workaround to display time in your current time zone. You can use following power query in the Query Editor if your time zone is 8 hours ahead of UTC.
DateTimeZone.SwitchZone( DateTimeZone.FixedLocalNow(), +8 )
Thank you for your response, Hebert. I'm still fairly new to PBI. Could you be more specific to exactly what I need to do?
When you say "You can use following power query in the Query Editor...", do you mean I need to create another time column in the query editor with that formula or do you mean I need to modify my existing date/time formulas I mentioned in the opening post with this fix? Or is it something totally different that I have not yet done in the query editor?
Hebert, yes I implemented this solution a couple of days ago, but I'm running into a new related problem. In PBI Desktop everything shows up correctly, I then publish to the cloud and everything was correct last night. It did not flip after 8pm - good. The problem is now it wouldn't flip back to "yesterday" once I pass midnight. Even this morning it was still showing what I published yesterday afternoon. I refreshed the data source on the cloud service and it's a direct query connection with an on-premise gateway, configured correctly and online.
Only once I went into PBI Desktop, refreshed the data there, did the report correct itself. Then when I publish the correct data from PBI Desktop back to the cloud does it now display correctly. Obviously, this is not something I can do every morning.
Any suggestions on why this is occurring?
@v-qiuyu-msft I saw a similar topic below. I have read it, but did you have any input on my issue?
@v-micsh-msft I saw a similar topic below. I have read it, but did you have any input on my issue?