Relative Date in Local time not UTC

[Anonymous]

The results I am trying to display on PowerBI is Yesterdays results. However I want these at about 8am in the morning when unfortunately it has not ticked over to the next day in UTC time. Therefore with my Relative date (Show last 1 day) fields are showing 2 days before and not yesterday. 

I have read through a couple solutions and I am not sure they work in my situation. I could use Todays data rather than Show last 1 Day however if I want to refresh the data after 1030am when it changes to the next day in UTC I will get Todays data which isn't there yet. 

The other solution I have seen is manipulating the date/time to UTC time but i can't see how this is possible as some of my data does not have time and just has a raw date. 

I have only been using BI for a couple days so any solutions you will probably have to step it out for me. Thank you for your help. 

[v-frfei-msft]

Hi @Anonymous,

Please try to set the locale both in Power BI Desktop and Service. See: Supported languages and countries/regions for Power BI.

Or you can change the date by DAX. We need to calculate the UTC datetime value use known time zone. Suppose the local time zone is UTC-8, to convert the UTC value to local datetime value, we can specify the DAX expression like below:

Column 4 = FORMAT(Table1[UTC],"MM/dd/YYYY") & "  " & REPLACE(FORMAT(Table1[UTC],"HH:mm:ss"),1,2,HOUR(Table1[UTC])-8)

Then change this calculated column data type as Date/Time.

Regards,

Frank

[Anonymous]

I don't have this a time field for some of my data and the ones I do are not combined with the date. I tried the formula using just the date and am not having any luck

[v-frfei-msft]

Hi @Anonymous ,

Could you please share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.

Regards,

Frank