DAX for records between previous day 9:00 PM and current day 6:00 AM?

[DRing]

I have data like below where calls are coming into a call center. The businuess users want a report they can check daily that shows them calls that came in "after hours", between the time of 9:00 PM of the previous day and 6:00 PM of the current day.

For example, on the morning of 1/31/2024, the report would show calls between 1/30/2024 9:00 PM and 1/31/2024 6:00 AM.

What DAX will allow me to identify calls in this way?

CALL_ID	CALL_TIME	PHONE_NUMBER	CALL_DURATION_SECONDS
1	1/30/24 12:08 PM	(xxx) xxx-xxxx	13
2	2/1/24 1:18 PM	(xxx) xxx-xxxx	10
3	1/31/24 2:54 PM	(xxx) xxx-xxxx	44
4	1/31/24 2:10 PM	(xxx) xxx-xxxx	19
5	2/1/24 2:15 PM	(xxx) xxx-xxxx	42
6	1/31/24 11:47 AM	(xxx) xxx-xxxx	60
7	2/1/24 10:39 AM	(xxx) xxx-xxxx	32
8	1/29/24 10:53 AM	(xxx) xxx-xxxx	5
9	1/29/24 11:12 AM	(xxx) xxx-xxxx	17
10	2/1/24 11:19 AM	(xxx) xxx-xxxx	54
11	1/30/24 1:28 PM	(xxx) xxx-xxxx	18
12	2/1/24 10:50 AM	(xxx) xxx-xxxx	19
13	2/1/24 1:48 PM	(xxx) xxx-xxxx	0
14	1/30/24 1:13 PM	(xxx) xxx-xxxx	53
15	1/31/24 2:25 PM	(xxx) xxx-xxxx	38
16	1/29/24 10:25 AM	(xxx) xxx-xxxx	39
17	1/29/24 11:43 AM	(xxx) xxx-xxxx	26
18	1/31/24 12:59 PM	(xxx) xxx-xxxx	7
19	1/29/24 1:51 PM	(xxx) xxx-xxxx	48
20	1/30/24 11:27 AM	(xxx) xxx-xxxx	29
21	2/1/24 1:24 PM	(xxx) xxx-xxxx	51
22	1/30/24 12:14 PM	(xxx) xxx-xxxx	2
23	1/29/24 11:37 AM	(xxx) xxx-xxxx	37
24	1/31/24 12:14 PM	(xxx) xxx-xxxx	57
25	1/29/24 1:37 PM	(xxx) xxx-xxxx	50
26	2/1/24 2:09 PM	(xxx) xxx-xxxx	57
27	2/1/24 1:13 PM	(xxx) xxx-xxxx	4
28	1/29/24 2:34 PM	(xxx) xxx-xxxx	56
29	1/29/24 10:05 AM	(xxx) xxx-xxxx	31
30	1/30/24 12:21 PM	(xxx) xxx-xxxx	8
31	2/1/24 2:31 PM	(xxx) xxx-xxxx	9

[Greg_Deckler · Accepted solution]

@DRing Try a Complex Selector like this: The Complex Selector - Microsoft Fabric Community

Selection Measure = 
    VAR __CallTime = MAX('Table2'[CALL_TIME])
    VAR __Today = TODAY()
    VAR __Yesterday = __Today - 1
    VAR __Start = __Yesterday + TIME(17, 0, 0)
    VAR __End = __Today + TIME(6, 0, 0)
    VAR __Result = IF( __CallTime >= __Start && __CallTime <= __End, 1, 0)
RETURN
    __Result

[v-junyant-msft · Accepted solution]

Hi @DRing ,

@Greg_Deckler Good answer!,
And you can also create a slicer, please try this way:
Here is my sample data:

And I create a new table for slicer:

There is no relationship between the two tables:

I use this DAX to create a measure:

CallsCount = 
VAR SELECTED_TIME = SELECTEDVALUE('Table 2'[Date])
VAR StartTime = (SELECTED_TIME - 1) + TIME(21, 0, 0)
VAR EndTime = SELECTED_TIME + TIME(6, 0, 0)
RETURN
IF(
    ISFILTERED('Table 2'[Date]),
    IF(
        MAX('Table'[CALL_TIME]) >= StartTime && MAX('Table'[CALL_TIME]) <= EndTime,
        1,
        0
    ),
    1
)

Put this measure into the "Filters on this visual":

The final output is below:

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

[Greg_Deckler · Accepted solution]

@DRing Try a Complex Selector like this: The Complex Selector - Microsoft Fabric Community

Selection Measure = 
    VAR __CallTime = MAX('Table2'[CALL_TIME])
    VAR __Today = TODAY()
    VAR __Yesterday = __Today - 1
    VAR __Start = __Yesterday + TIME(17, 0, 0)
    VAR __End = __Today + TIME(6, 0, 0)
    VAR __Result = IF( __CallTime >= __Start && __CallTime <= __End, 1, 0)
RETURN
    __Result