"Is in this day" filter

[uthall]

Hi,

Just wondering how the report determines what the current day is when using the visual filter (Relative date filtering) "Is in this day"

Does it use the sysrem time of the report server (if using a report server and viewing via a browser), the local time of the computer running the report (if running local), or the power bi service system time (if using web)?

[v-diye-msft]

Hi @uthall ,

The date slicer and filter are always based on the time in UTC, it is the power bi service system time in web. so if you configure a filter in a report and send it to a colleague in a different time zone, you'll both see the same data. However, if you aren't in the UTC time zone, you might see data for a different time offset than you expect.

Furthermore, you could convert the UTC time to local time using query editor:

Assuming that you already have the UTC time and time lag column:

1. Change the UTC time with accurate time zone (UTC+0 in this example)

ZoneTime = DateTime.AddZone([Date],0)

2. Switch the time zone by calculating the time lag:

LocalTime = DateTimeZone.SwitchZone([ZoneTime],[UTC Duration])

3. Then change the date type to Date/Time/TimeZone

Regards,

Dina

[uthall]

Thanks Dina,

I already have "Local Time" in my table, so its not the data, its what the filter is using as the reference day.

What i find, is that when i run the report from my desktop (UTC +8), its not untill 8am in the morning that i start to see todays data in the report using the "Is in this day" filter.

The report shows yesterdays data before 8am.

So, is the PowerBI desktop client using UTC+0 to determine the current day, and if so, how do i get it to use the "current" day in my timezone....for me UTC+8 

[v-diye-msft · Accepted solution]

Hi @uthall ,

The Power bi desktop using UTC+0  to determine the current day, you’ll need to add a measure to show NOW time and filter your data, and only the records that the date equal to Date(NOW), then filter applied. Steps shown as below:

Please note that once you published your report to service, the measure is different, displaying the UTC+0 time.

1. I’ve created a calendar table with the dates from 6/1 to 6/30 this year , and add a measure NOW show the current UTC+8 time.

2. Another measure added to filter the date which equals to Now day, if equals, then return 1, or 0.

Measure = var NY = YEAR([N])
var NM = MONTH([N])
var DN = DAY([N])
var time = MAX([Date])
Return
IF(YEAR(time)=NY&&MONTH(time)=NM&&DAY(time)=DN,1,0)

3.Then filter the result is 1 and apply filter.

Regards,

Dina

[CmdrKeene]

I did this and it looks correct if I make a visual to show my calendar table the only day with the measure=1 is today's date, but I can't move that measure into the filter pane in Power BI. What gives?