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

[minishshah]

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/

In another words, I created a table with time shift value of 5. Then I created the following calculated column to shift the value which works for Today's sales data:

Local Today =
VAR LocalTime = UTCNOW() - TIME(DISTINCT(Timeshift[Hours]), 0, 0)
RETURN DATE(YEAR(LocalTime), MONTH(LocalTime), DAY(LocalTime))

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.

[v-shex-msft]

Hi @minishshah,

AFAIK, current power bi service does not support local timezone date value, it will convert these DateTime values to UTC format. For this scenario, you can create a calculated column to manually setting offset on DateTime value to achieve offset UTC value in power bi service side. 
These steps work to modify the values of the field that you use on visuals, but they do not affect the refresh settings. (power bi will keep used UTC DateTime to trigger the refresh)  So I think you need to check your refresh settings to choose the correct trigger times.

Regards,

Xiaoxin Sheng

[minishshah]

@v-shex-msft and @Anonymous, Thank you both for replying back to my post.

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.

[v-shex-msft]

HI @minishshah,

If you are work with the scheduled refresh, I think you need to set the refresh timezone to use your local timezone to trigger the refresh:

Configure scheduled refresh#scheduled-refresh 

If you want to achieve condition or accurate refreshes, you can also take a look following blog to use power bi automate and ms flow:

Refresh your Power BI dataset using Microsoft Flow 

Regards,

Xiaoxin Sheng

[minishshah]

Thank you! This is helpful, but not to my current situation. 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-shex-msft]

HI @minishshah,

I did not have some great ideas for setting scheduler refresh to achieve your requirement.
If you do not worry about redundancy data, you can try to create a new calculate tablt to filter raw table records based on the system date and table DateTime.
Then you can use these new table records to design visaulzations and you not need to worry about the data refresh, table records will be controlled based on expression level and the dataset update not force change its records.
Regards,

Xiaoxin Sheng

[minishshah]

This is helpful, but not to my current situation. 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.

[minishshah]

I am still looking for the solution. Any assistance that can be provided would be greatly appreciative.

[Anonymous]

Make sure you select the correct timezone.