Last x amount of hours as filter

[v_mark]

I was trying to filter a table based on created date time on the last 12 hours.  But it's not giving me the result

In the date base, I have 3 entries that were logged in the last 12 hours but in PBI I can only capture 1 .

Right now is 

I was hoping to use the last 12 as a way to filter a table or matrix which I just have the columns exposed 
and probably change the x amount of hours for future needs 

@Greg_Deckler   Thank you for the guidance 

I have the table that looks like below.

out of 5, I should see only 3 as part of the last 12 hours (let's say its 9:07 CST AM now )

I'm planning to use it as a filter to show only the last 12 hours entries based on the current(now) time. 

so A B C will only show on the table/matrix

Ticket	CreatedDateTime	Status
A	5/6/2021 8:47	New
B	5/6/2021 0:38	New
C	5/6/2021 0:34	New
D	5/5/2021 3:37	New
E	5/5/2021 0:04	New

Thank in advance..

[v-jingzhang · Accepted solution]

Hi @v_mark 

Sorry for disturbing but I have another idea about this issue. When using the relative time slicer or filter, there is a concept anchor time which will influence the filtered time range. In your screenshot, the anchor time is 1:53:25 PM which is obviously different from the current time 9:07:02 AM. So it filters the data in the last 12 hours of anchor time instead of the actual current time. 

The relative time slicer and filter are always based on the time in UTC. However, data models in Power BI don't include time zone info. If your time data is not UTC time, you will have this problem. A workaround is to change your time column into UTC time or add a new column which converts your time into UTC time in Power Query Editor. Then add this new UTC time column as a filter field to the table/matrix's visual-level filters and apply the relative time filter on it. 

For example

    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"CreatedDateTime", type datetimezone}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "UTC time", each DateTimeZone.ToUtc([CreatedDateTime])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"UTC time", type datetimezone}})

Reference: Use a relative time slicer or filter in Power BI - Power BI | Microsoft Docs

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[v_mark]

@v-jingzhang @Greg_Deckler 
This makes sense! Thank you so much! Will this approach works on global users. I do have power BI Consumers who located around the globe. Will the time is dynamic enough to follow their local time?

[v-jingzhang]

Hi @v_mark 

The relative time slicer and filter are based on UTC time, so the global users will see the same filtered result. What dynamic time are you hoping to see? The displayed CreatedDateTime change dynamically according to the users' local time?

Regards,

Jing

[Greg_Deckler]

@v_mark Well, for the 5/5 tickets, the only way that they would be included in the last 12 hours is if it was still 5/5. Once you get to 12:05 pm on the 5th, ticket E would fall off. Ticket D would fall off at 3:28 PM on the 5th. So once it is the 6th, none of the 5/5 tickets are going to show, you would have to go back 48 hours or so to get all of the tickets to show up at 7:00PM on the 6th. 

[v_mark]

Is there a posibility to create a last 6 and 12 filters for future use? 

[Greg_Deckler]

@v_mark HSorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[v_mark]

@Greg_Deckler Thank you. For some reason I cannot reply with additional info due to duplicates entries etc.
I updated the post above