cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rhilliard Frequent Visitor
Frequent Visitor

PBI Desktop is flipping yesterday and today when publishing to PBI Cloud

AmCloud.PNGAmDesktop.PNG

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)

5 REPLIES 5
v-haibl-msft Super Contributor
Super Contributor

Re: PBI Desktop is flipping yesterday and today when publishing to PBI Cloud

@rhilliard

 

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 )

 

Best Regards,
Herbert

Highlighted
rhilliard Frequent Visitor
Frequent Visitor

Re: PBI Desktop is flipping yesterday and today when publishing to PBI Cloud

 

@v-haibl-msft

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?

v-haibl-msft Super Contributor
Super Contributor

Re: PBI Desktop is flipping yesterday and today when publishing to PBI Cloud

@rhilliard

 

You need to create a new date time column in Query Editor as following screenshot.

 

PBI Desktop is flipping yesterday and today when publishing to PBI Cloud_1.jpg

 

Best Regards,
Herbert

rhilliard Frequent Visitor
Frequent Visitor

Re: PBI Desktop is flipping yesterday and today when publishing to PBI Cloud

@v-haibl-msft 

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?

rhilliard Frequent Visitor
Frequent Visitor

Re: PBI Desktop is flipping yesterday and today when publishing to PBI Cloud

@v-qiuyu-msft I saw a similar topic below. I have read it, but did you have any input on my issue?

Re: Direct Query is not automatically refreshing report, but is automatically refreshing Dashboard

 

@v-micsh-msft I saw a similar topic below. I have read it, but did you have any input on my issue?

Re: Refreshing Dataset when using Direct Query