Sales Data from Yesterday and Today in Context of Time zone of the Power Bi Services

[minishshah]

Hello,

I am in need of help. I have a dashboard that shows today's sales data. However, any time this data set refreshes after 6:00 PM EST, the data from this dashboard disappeared as Power Bi Services are running on a server that is somewhere in Europe, 5 hours ahead of us in Eastern Standard Time.

I then came across the following life saver article that allowed me to keep the today's sales in view until it turns midnight. Here is the link to the article: https://4pbi.com/resolving-timezone-issue-on-power-bi-service/

I also have a dashboard that shows Yesterday's sales. However, I am running into the same problem as I was with Today's sales prior to applying the fix. Again, the data about yesterday's sales disappears and gets replaced with Today's sales any time the data set refreshes after 6:00 PM EST.  I also want to keep the Yesterday's Sales in view until midnight also upon when it changes, but just don't know how i can accomplish this. Any assistance would be greatly appreciated.

[minishshah]

Thank you for replies, but I am still looking for the solution. Any assistance that can be provided would be greatly appreciative.

I am all set with scheduled refresh as i set the triggers every 2 hours. The problem is that the dashboard does not keep and show previous day data after 8:30 PM EST refresh because UTC time now shows 1:30 AM which is the next day, but not according to EST Time. In another words, I want to keep showing Previous day data until the UTC time is 5:00 AM.

[v-cazheng-msft]

Hi, @minishshah 

How about selecting the Time zone in dataset Setting according to your country? Power BI Service will refresh according to the configuration here.

Best Regards,

Caiyun Zheng

[minishshah]

Thank you, but the above did not help. 

What I am really trying to accomplish is as following:

I want to be able to report on Previous day sales number via a dashboard, but I do not want the previous day data to disappear from this dashboard until it is truly the next day per my time zone, Eastern Standard Time (EST). 

I currently have a Date Last Refreshed table with a column derived from the following M Code:
= #table(type table[Date Last Refreshed=datetime], {{DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-5)}})

I also have another column in that table with an offset hour (a value of 5 for 5 hours difference between EST and UTC) and a calculated column that offsets the current UTC time minus 5 as following: 

Local Today =

VAR LocalTime = UTCNOW() - TIME(DISTINCT('Date Last Refreshed'[Hours]),0,0)

RETURN DATE(YEAR(LocalTime), MONTH(LocalTime), DAY(LocalTime))

I also have another calculated column in that same table that I use as a timestamp on the dashboard which shows EST time as following: 

Local = 'Date Last Refreshed'[Date Last Refreshed] - (5/24)

All I want to do, is show the Previous Day Sales per "Local Today" calculated column measure above. In another words, this dashboard should not refresh until it is 12:00 Midnight EST time.

[biki]

i'm facing the same issue here, only my timezone is UTC+7 which is opponent from yours.

my problem is i have to show yesterday's sales 7 hours ahead of UTC, now i have to wait until 7AM in the morning and still need time to refresh all the related data until 9AM, it's too late for us. 

i used the sql server and the data stores date in UTC, but whenever shows in any apps it's always UTC+7. but how in PowerBI this rule does not apply? 

i've already tried many ways:

1. change the report regional option in powerbi desktop 
2. create measure: 

YESTERDAY = Date(FORMAT(NOW()+(7/24)-1,"YYYY"),FORMAT(NOW()+(7/24)-1,"MM"),FORMAT(NOW()+(7/24)-1,"DD")) and then calculate the value based on date filter YESTERDAY

but still the data does not show as expected. before 9AM, it's still the day before yesterday's sale data.

so i'm still waiting for the perfect solution on this.

thanks!

biki

[lbendlin]

You are on the right track, but you need to add/subtract 7/24 to the actual datetime field, not to the string representation.   Kinda like DATEADD but with time values.

[v-cazheng-msft]

Hi, @minishshah 

You can refer Current date, Solving DAX Time Zone Issue in Power BI and Dynamic time zone conversion using Power BI.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Power BI Service runs on UTC (as it should)

https://radacad.com/solving-dax-time-zone-issue-in-power-bi