Filter by time as well as date

[emilyrichardson]

Hi

I'm trying to find a way where I can get power BI to allow the user to filter by time and date. 

The idea is that the user will be able to filter to anything that was added to the system between 5pm yesterday and 5pm today (and they would view this the following morning). It gets a little bit more tricky over the weekend, as the user should be able to filter to anything added to the system from Thursday 5pm to Sunday 5pm. I can do this filter easily in the filter pane by dragging the date field there and specifying times, but I want the user to be able to do that. 

Here is some example data

Prod Number	Time presentation sent
1	19/07/2024 10:51
2	19/07/2024 11:55
3	19/07/2024 13:30
4	19/07/2024 13:54
5	22/07/2024 17:48
6	23/07/2024 05:55
7	23/07/2024 06:10
8	23/07/2024 17:05
9	24/07/2024 07:27
10	24/07/2024 10:03
11	25/07/2024 13:26
12	25/07/2024 15:21
13	25/07/2024 15:36
14	26/07/2024 06:52
15	26/07/2024 08:00
16	26/07/2024 08:24
17	26/07/2024 11:32
18	27/07/2024 07:43
19	27/07/2024 08:52
20	27/07/2024 10:52
21	27/07/2024 11:05
22	29/07/2024 13:24
23	29/07/2024 14:23
24	29/07/2024 16:55
25	30/07/2024 08:03
26	30/07/2024 10:05
27	30/07/2024 16:21
28	31/07/2024 07:38
29	31/07/2024 08:47
30	31/07/2024 15:55
31	31/07/2024 19:26
32	01/08/2024 09:49
33	01/08/2024 15:04
34	02/08/2024 10:15
35	02/08/2024 11:11
36	02/08/2024 17:32
37	03/08/2024 05:56
38	03/08/2024 11:56
39	05/08/2024 06:03
40	05/08/2024 06:14
41	05/08/2024 09:40
42	05/08/2024 16:19
43	05/08/2024 17:48
44	06/08/2024 08:09
45	06/08/2024 16:11
46	07/08/2024 06:34
47	07/08/2024 00:00
48	08/08/2024 05:59
49	08/08/2024 06:32
50	08/08/2024 06:33
51	08/08/2024 07:07
52	08/08/2024 07:10
53	09/08/2024 05:38
54	09/08/2024 07:43
55	09/08/2024 07:46
56	12/08/2024 06:04
57	12/08/2024 07:03
58	12/08/2024 08:19
59	12/08/2024 08:19
60	13/08/2024 12:01
61	14/08/2024 05:35
62	14/08/2024 07:47
63	14/08/2024 11:38
64	14/08/2024 13:30
65	15/08/2024 05:55
66	16/08/2024 09:15
67	19/08/2024 05:49
68	19/08/2024 05:57
69	19/08/2024 10:09
70	20/08/2024 08:44
71	20/08/2024 11:05
72	22/08/2024 06:04
73	23/08/2024 05:45
74	23/08/2024 06:02
75	27/08/2024 17:01
76	28/08/2024 15:23
77	28/08/2024 16:04
78	28/08/2024 16:57
79	28/08/2024 21:11
80	29/08/2024 06:03
81	29/08/2024 11:01
82	29/08/2024 18:53
83	30/08/2024 08:26
84	30/08/2024 10:12
85	02/09/2024 05:48
86	02/09/2024 13:57
87	02/09/2024 14:19
88	02/09/2024 16:36
89	02/09/2024 16:52
90	03/09/2024 06:00
91	03/09/2024 12:04
92	04/09/2024 08:04
93	04/09/2024 17:16
94	04/09/2024 17:29
95	04/09/2024 17:44
96	05/09/2024 08:06
97	05/09/2024 08:18
98	05/09/2024 08:26
99	05/09/2024 12:35
100	06/09/2024 05:56
101	06/09/2024 09:08
102	06/09/2024 20:10
103	09/09/2024 05:49
104	10/09/2024 05:32
105	10/09/2024 08:07
106	10/09/2024 10:22
107	10/09/2024 13:32
108	10/09/2024 15:04
109	11/09/2024 05:41
110	11/09/2024 06:47
111	11/09/2024 12:36
112	11/09/2024 15:26
113	11/09/2024 19:58
114	12/09/2024 05:33
115	12/09/2024 19:03
116	12/09/2024 19:17
117	12/09/2024 22:02
118	13/09/2024 05:28
119	13/09/2024 17:44
120	13/09/2024 23:16
121	16/09/2024 08:13
122	16/09/2024 08:13
123	16/09/2024 08:13
124	16/09/2024 10:22
125	17/09/2024 05:57
126	17/09/2024 06:32

If anyone has a way of doing this it would be greatly appreciated. Thank you! 

[v-yilong-msft]

Hi @emilyrichardson ,

I create a table as you mentioned.

Then I add EntryDateTime column into a slicer and choose Relative Date Style.

So you can do what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elitesmitpatel]

Please share the dummy pbix file for easy reference

[emilyrichardson]

Hi, thanks for the response! Here is some dummy data. 

Prod Number	Time presentation sent
1	19/07/2024 10:51
2	19/07/2024 11:55
3	19/07/2024 13:30
4	19/07/2024 13:54
5	22/07/2024 17:48
6	23/07/2024 05:55
7	23/07/2024 06:10
8	23/07/2024 17:05
9	24/07/2024 07:27
10	24/07/2024 10:03
11	25/07/2024 13:26
12	25/07/2024 15:21
13	25/07/2024 15:36
14	26/07/2024 06:52
15	26/07/2024 08:00
16	26/07/2024 08:24
17	26/07/2024 11:32
18	27/07/2024 07:43
19	27/07/2024 08:52
20	27/07/2024 10:52
21	27/07/2024 11:05
22	29/07/2024 13:24
23	29/07/2024 14:23
24	29/07/2024 16:55
25	30/07/2024 08:03
26	30/07/2024 10:05
27	30/07/2024 16:21
28	31/07/2024 07:38
29	31/07/2024 08:47
30	31/07/2024 15:55
31	31/07/2024 19:26
32	01/08/2024 09:49
33	01/08/2024 15:04
34	02/08/2024 10:15
35	02/08/2024 11:11
36	02/08/2024 17:32
37	03/08/2024 05:56
38	03/08/2024 11:56
39	05/08/2024 06:03
40	05/08/2024 06:14
41	05/08/2024 09:40
42	05/08/2024 16:19
43	05/08/2024 17:48
44	06/08/2024 08:09
45	06/08/2024 16:11
46	07/08/2024 06:34
47	07/08/2024 00:00
48	08/08/2024 05:59
49	08/08/2024 06:32
50	08/08/2024 06:33
51	08/08/2024 07:07
52	08/08/2024 07:10
53	09/08/2024 05:38
54	09/08/2024 07:43
55	09/08/2024 07:46
56	12/08/2024 06:04
57	12/08/2024 07:03
58	12/08/2024 08:19
59	12/08/2024 08:19
60	13/08/2024 12:01
61	14/08/2024 05:35
62	14/08/2024 07:47
63	14/08/2024 11:38
64	14/08/2024 13:30
65	15/08/2024 05:55
66	16/08/2024 09:15
67	19/08/2024 05:49
68	19/08/2024 05:57
69	19/08/2024 10:09
70	20/08/2024 08:44
71	20/08/2024 11:05
72	22/08/2024 06:04
73	23/08/2024 05:45
74	23/08/2024 06:02
75	27/08/2024 17:01
76	28/08/2024 15:23
77	28/08/2024 16:04
78	28/08/2024 16:57
79	28/08/2024 21:11
80	29/08/2024 06:03
81	29/08/2024 11:01
82	29/08/2024 18:53
83	30/08/2024 08:26
84	30/08/2024 10:12
85	02/09/2024 05:48
86	02/09/2024 13:57
87	02/09/2024 14:19
88	02/09/2024 16:36
89	02/09/2024 16:52
90	03/09/2024 06:00
91	03/09/2024 12:04
92	04/09/2024 08:04
93	04/09/2024 17:16
94	04/09/2024 17:29
95	04/09/2024 17:44
96	05/09/2024 08:06
97	05/09/2024 08:18
98	05/09/2024 08:26
99	05/09/2024 12:35
100	06/09/2024 05:56
101	06/09/2024 09:08
102	06/09/2024 20:10
103	09/09/2024 05:49
104	10/09/2024 05:32
105	10/09/2024 08:07
106	10/09/2024 10:22
107	10/09/2024 13:32
108	10/09/2024 15:04
109	11/09/2024 05:41
110	11/09/2024 06:47
111	11/09/2024 12:36
112	11/09/2024 15:26
113	11/09/2024 19:58
114	12/09/2024 05:33
115	12/09/2024 19:03
116	12/09/2024 19:17
117	12/09/2024 22:02
118	13/09/2024 05:28
119	13/09/2024 17:44
120	13/09/2024 23:16
121	16/09/2024 08:13
122	16/09/2024 08:13
123	16/09/2024 08:13
124	16/09/2024 10:22
125	17/09/2024 05:57
126	17/09/2024 06:32

[Selva-Salimi]

hi @emilyrichardson 

I am not sure if I understand your issue, but in the way that user be able to filter any date and any hour you can add slicer in the dashboard.

but in fact I don't think that it was the issue. I think that you want to dynamically update dashboard based on last previous working day. if so, let me know.

[emilyrichardson]

Hi, thanks for getting back to me! I don't need the power bi to refresh dynamically, but if that is the only way around it happy to set that up. I need the user to specifiy what date they would like to view and power bi to know they mean between 5 to 5. I've posted some dummy data to the intial post, as using a slicer I can't work out how to get the slicer to look at date as well as time. I can't use within last lets say 12 hours as I'm not sure when the user will be looking at the dashboard to see the data. 

[Selva-Salimi]

@emilyrichardson 

you can add an slicer to the dashboard and use "relative time" option in slicer setting. if it does not fulfill your expectations then you can do the following steps:

1. create a column Hour = FORMAT(Time_presentation , "HH")

2. add an slicer to the dashboard, add time_presentation which is in YYYY-MM-DD format and create a hierarchy under date which is the hour column you craete before, in this situation end user can select date and also time.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[emilyrichardson]

@Selva-Salimi thank you for your response. 

I have done it slightly differently based off of your response. All I have done is split the date and time into two seperate columns and added that to the slicer, my only issue is that its a drop down and doesn't allow me to do an inbetween, as that would reduce human error, where I am only asking the user to enter the two dates they want to look between and not select all the dates and times. 

[Selva-Salimi · Accepted solution]

@emilyrichardson 

then you need to have another table for your timestamps (like DimDate) and dont use any relation between this table and your table. and write a measure as follows:

measure inbetween := var min_date = min(Dimdate[date_time])

var max_date = max(Dimdate[date_time])

return

if( your_table [timestamp] >=min_date && your_table[timestamp] <=max_date , 1 , 0 )

add this to filters of page and set it to 1.

then you can ask your end user to select two time dates ( not all in between) to see result. this is the easiet solution I recommend but there are other solutions too.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.