Do you know the Anchor Time in Relative Time Filter and Slicer?

[v-rzhou-msft]

When you are new to use the relative time filter and relative time slicer in a report, you might find that the filtered records are not consistent with that you observe the results manually. For example, I have some ticket data as below. I hope to see the records created in the last 12 hours of the current time, so I would like to use a relative time slicer in the report. The situation in the relative time filter is similar.

Let’s say it is 8/9/2021 1:19:37 PM now, so the ticket E & F should display in the report. However, when I set last 12 hours on the slicer, it shows tickets A/B/C/D/E instead of the desired E and F. These records were not created in the last 12 hours of the current time. In addition, you will find that under the relative time slicer, it shows the time range from 8/8/2021 5:19:36 PM to 8/9/2021 5:19:36 AM. This is not the previous 12 hours of the current time. After I add a UTC time in the report, apparently the time range is the last 12 hours of the UTC time rather than my local time.

To dig deep about this, I searched and found the official document about the relative time filter and slicer. In the document, it clearly introduces a concept of anchor time. When a relative time filter or slicer is applied, these queries are all issued relative to a time called the anchor time. And the slicer and filter are always based on the time in UTC, so their default anchor time is UTC time. If your local time is different from the UTC time, you will find the filtered result is not consistent with your expectation.

Not all users are located in UTC time regions. To deal with this problem, we could convert our local datetime column to UTC time specially for the relative time filter and slicer. We could add a UTC time column into the model by using Power Query Editor or DAX calculated column. We can also add this column at the data source side.

Custom column in Query Editor (my local time is UTC+08:00):

= DateTimeZone.ToUtc(DateTime.AddZone([CreatedDateTime],8))

DAX calculated column (my local time is UTC+08:00):

UTC Time = 'Table'[CreatedDateTime] - 8/24

In the report, put the new UTC time column into the relative time filter and slicer, now it works as expected. At the same time, you can display local datetime column in the report.

Notice:

1. If you use Power Query Editor to add a UTC time column, change its data type to DateTimeZone rather than DateTime. Then apply the change to the model. If you change it back to DateTime type in Power Query Editor, it will switch back to local time automatically. After applying the change, change the UTC time column to date/time type in the model. Otherwise, it will display the UTC time in the Text data type and cannot be used in the relative time slicer.

2. The anchor time automatically refreshes in the following conditions:

• Initial page load.
• Manual refresh.
• Automatic or change detection page refresh.
• A change to the model.

If you simply change the hours in the slicer or filter, the anchor time will not be updated automatically. You need to refresh the report the get a newer anchor time.

Author: Jing Zhang

Reviewer: Ula Huang, Kerry Wang

relative time slicer and filter.pbix