I have a Fact table which has many rows, with a StatusFrom (e.g. 2025-03-17 12:54:55) and StatusTo (e.g. 2154:01:01 00:00:00) and I am looking to select ONLY the rows where SELECTEDDATE (+23:59:59) is between StatusFrom and StatusTo. This FACT is a fact showing the status changes for a case over a period of time, and we only want to see the status's as at 23:59:59 of whichever selected date we use, so we can look at this retrospectively too.

I have followed all of the tutorials and cannot get anything to work as planned.

I have got this far:

Is Date Between Status 2 =
VAR SelectedDate = SELECTEDVALUE('Dim Date'[Date])  -- Get the selected date from the slicer or context
VAR DateTimeAtMidnight = SelectedDate + TIME(23, 59, 59)  -- Combine the date with '23:59:59' time
RETURN
IF (
    DateTimeAtMidnight >= SELECTEDVALUE('Fact Device Status'[StatusFromDateTime]) &&
    DateTimeAtMidnight <= SELECTEDVALUE('Fact Device Status'[StatusToDateTime]),
    1,  -- Returns 1 if the selected date (with '23:59:59' time) is between StatusFrom and StatusTo
    0   -- Returns 0 otherwise
)

but it returns this data when selected today: Maverikk_0-1743145083647.png.

DimDate is joined to Fact on Date = StatusFromDate. I guess I am doing something wrong but need some direction please.